Rotate table 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 sult 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 EndangeredHopefully this query can help you:
select c.species_code, max(c.State_Status_Desc) as 'State_Status_Desc',
max(c.Federal_Status_Desc) as 'Federal_Status_Desc'
from
(select a.species_code,
case when b.Category_Code = 'S' then b.Status_Desc else null end as
'State_Status_Desc',
case when b.Category_Code = 'F' then b.Status_Desc else null end as
'Federal_Status_Desc'
from
#TAXO_STATUS a , #TAXO_STATUS_LUT b
where a.Taxo_Listing_ID = b.Taxo_Listing_ID) c
group by c.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 sult 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
>|||Thanks to Trey my query has been solved
select 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
On Fri, 18 Nov 2005 18:41:35 GMT, wawork@.hotmail.com (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 sult 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