cross tab question

I am trying to create a cross tab query to to combine the rows for two dates
on one line:
DATA:
( query to get this data:
select top 6
stkhstCsiSym,
stkhstDATE,
stkhstXO
from stkhst
where stkhstdate >=20050225
order by stkhstcsisym, stkhstDATE)
1006 20050225 O
1006 20050228 X
1008 20050225 O
1008 20050228 O
1012 20050225 O
1012 20050228 X
I wish the result to be
1006 O X
1008 O O
1012 O X
I've tried the following query that does not work:
select top 6
stkhstCsiSym,
CASE stkhstDate WHEN 20050225 THEN stkhstXO END AS FXO,
CASE stkhstDATE WHEN 20050228 THEN stkhstXO END AS SXO
from stkhst
where stkhstdate >=20050225
group by stkhstCsiSym, stkhstDATE, stkhstXO
order by stkhstcsisym, stkhstDATE:
this gives me :
1006 O NULL
1006 NULL X
1008 O NULL
1008 NULL O
1012 X NULL
1012 NULL O
Other than the fact that it does not work does anyone have anyideas how to
do this correctly?
thanks
kesShould i be using a join instead?
thanks
"Kurt Schroeder" wrote:

> I am trying to create a cross tab query to to combine the rows for two dat
es
> on one line:
> DATA:
> ( query to get this data:
> select top 6
> stkhstCsiSym,
> stkhstDATE,
> stkhstXO
> from stkhst
> where stkhstdate >=20050225
> order by stkhstcsisym, stkhstDATE)
> 1006 20050225 O
> 1006 20050228 X
> 1008 20050225 O
> 1008 20050228 O
> 1012 20050225 O
> 1012 20050228 X
> I wish the result to be
> 1006 O X
> 1008 O O
> 1012 O X
> I've tried the following query that does not work:
> select top 6
> stkhstCsiSym,
> CASE stkhstDate WHEN 20050225 THEN stkhstXO END AS FXO,
> CASE stkhstDATE WHEN 20050228 THEN stkhstXO END AS SXO
> from stkhst
> where stkhstdate >=20050225
> group by stkhstCsiSym, stkhstDATE, stkhstXO
> order by stkhstcsisym, stkhstDATE:
> this gives me :
> 1006 O NULL
> 1006 NULL X
> 1008 O NULL
> 1008 NULL O
> 1012 X NULL
> 1012 NULL O
> Other than the fact that it does not work does anyone have anyideas how to
> do this correctly?
> thanks
> kes|||As a general approach, one could use:
SELECT col1,
MAX( CASE col2 WHEN '20050225'
THEN col3
END ) AS "somecol1",
MAX( CASE col2 WHEN '20050228'
THEN col3
END ) AS "somecol2"
FROM tbl
GROUP BY col1 ;
Anith|||hey! that works!! thanks. I'm using something else a self join, but i'd have
used this if i saw it first.
thank you
kes
"Anith Sen" wrote:

> As a general approach, one could use:
> SELECT col1,
> MAX( CASE col2 WHEN '20050225'
> THEN col3
> END ) AS "somecol1",
> MAX( CASE col2 WHEN '20050228'
> THEN col3
> END ) AS "somecol2"
> FROM tbl
> GROUP BY col1 ;
> --
> Anith
>
>