Rotate table query help

I need to join two tables I guess in a rotate fashion but have not
been able to figure it out. We have a Taxo_Status table that contains
up to 2 entries per species. These records need to be joined
Taxo_Status_LUT table such that one record is turned per species with
a state status description (category_code = "S") and a federal status
description (category_code = "F").
This is the result set I need
Species_Code State_Status_Desc Federal_Status_Desc
HALE Endangered Threatened
KALA null Sensitive
MAMU Endangered null
TAXO_STATUS Table
Species_Code Taxo_Listing_ID
HALE 3
HALE 5
MAMU 3
KALA 4
TAXO_STATUS_LUT Table
Taxo_Listing_ID Category_Code Status_Desc
1 S Sensitive
2 S Threatened
3 S Endangered
4 F Sensitive
5 F Threatened
6 F Endangeredselect ts.species_code,
max(case when category_code='S' then status_desc end) as
Status_Status_Desc,
max(case when category_code='F' then status_desc end) as
Federal_Status_Desc
from taxo_status ts
join taxo_status_lut tsl on ts.taxo_listing_id = tsl.taxo_listing_id
group by ts.species_code
order by ts.species_code
Randy K wrote:
> I need to join two tables I guess in a rotate fashion but have not
> been able to figure it out. We have a Taxo_Status table that contains
> up to 2 entries per species. These records need to be joined
> Taxo_Status_LUT table such that one record is turned per species with
> a state status description (category_code = "S") and a federal status
> description (category_code = "F").
> This is the result set I need
> Species_Code State_Status_Desc Federal_Status_Desc
> HALE Endangered Threatened
> KALA null Sensitive
> MAMU Endangered null
> --
> TAXO_STATUS Table
> Species_Code Taxo_Listing_ID
> HALE 3
> HALE 5
> MAMU 3
> KALA 4
> --
> TAXO_STATUS_LUT Table
> Taxo_Listing_ID Category_Code Status_Desc
> 1 S Sensitive
> 2 S Threatened
> 3 S Endangered
> 4 F Sensitive
> 5 F Threatened
> 6 F Endangered