cross tab needs in SQL
INFORMIX-OnLine Version 7.23.UC11 ISQL-7.20.UD1 HP-UX 11.0 Is there a way to do a cross tab output in ISQL? I don't have a data cube function in 7.2, and I need one. The only alternative I've found is to do cut and paste in a UNIX shell script (can you spell slow?). I have tabular data and I need it to be cross tab. What I have: ID|DAYS|CNT 01|0001|25 01|0004|20 02|0001|10 03|0020|10 03|0025|15 What I need: ID|1 Day|4 Day |20 Day|25 Day| 01| 25 | 20 | | | 02| 10 | | | | 03| | | 10 | 15 | Thanks. Rob Konikoff
If you were running a later version you could use the great multiset
stuff, but you are not so you are going to have to do it the old
fashioned straight SQL way (yuck):
Assuming original table looks like this:
create table date_test(
id integer,
days integer
) ;
where you have the following entries:
(1, 1) 25 times
(1, 4) 20 times
(2, 1) 10 times
(3, 20) 10 times
(3, 25) 15 times
select
t.id,
sum(
case days
when 1 then 1
else 0
end
) day1,
sum(
case days
when 4 then 1
else 0
end
) day4,
sum(
case days
when 20 then 1
else 0
end
) day20,
sum(
case days
when 25 then 1
else 0
end
) day25
from
date_test t
group by
id
;