Re: cross tab needs in SQL
Hi Ron,
No the best solution but if you columns are fixed try this
drop table pru; drop table resu;
create table pru (ID char(2),DAYS smallint,CNT int);
insert into pru values (01,0001,25);
insert into pru values (01,0004,20);
insert into pru values (02,0001,10);
insert into pru values (03,0020,10);
insert into pru values (03,0025,15);
select id, sum(cnt) day_1, 0 day_4, 0 day_20, 0 day_25
from pru where days = 1 group by id
into temp resu;
insert into resu
select id, 0, sum(cnt), 0, 0 from pru where days = 4 group by id;
insert into resu
select id, 0, 0, sum(cnt), 0 from pru where days = 20 group by id;
insert into resu
select id, 0, 0, 0, sum(cnt) from pru where days = 25 group by id;
select id, sum(day_1) day_1,
sum(day_4) day_4,
sum(day_20) day_20,
sum(day_25) day_25
from resu
group by 1
order by 1;
When run I got
[cte@adela tmp]$ dbaccess paso a.sql
Database selected.
Table dropped.
206: The specified table (resu) is not in the database.
111: ISAM error: no record found.
Error in line 1Near character position 31
Table created.
1 row(s) inserted.
1 row(s) inserted.
1 row(s) inserted.
1 row(s) inserted.
1 row(s) inserted.
2 row(s) retrieved into temp table.
1 row(s) inserted.
1 row(s) inserted.
1 row(s) inserted.
id day_1 day_4 day_20 day_25
1 25 20 0 0
2 10 0 0 0
3 0 0 10 15
3 row(s) retrieved.
Database closed.
HTH
Konikoff, Rob (Contractor) escribió:
>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
>
>
>_______________________________________________
>Informix-list mailing list
>Informix-list@iiug.org
>http://www.iiug.org/mailman/listinfo/informix-list
>
>
>
>