SEO

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 1
Near 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
>
>
>
>

You are doing more work than you need to with this table structure. The
table is already grouped by id for each different day in your
representation so there is no reason to do a sum or group by.
You only have to do the following:

select id, cnt day_1, 0 day_4, 0 day_20, 0 day_25
from pru where days = 1
into temp resu;
insert into resu
select id, 0, cnt, 0, 0 from pru where days = 4;
insert into resu
select id, 0, 0, cnt, 0 from pru where days = 20;
insert into resu
select id, 0, 0, 0, cnt from pru where days = 25;

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;

Notice that you only have to group in the last query which is
essentially putting the id records together.

with this table structure my SQL would look like:
select
t.id,
sum( case days
when 1 then cnt
else 0
end
) day1,
sum( case days
when 4 then cnt
else 0
end
) day2,
sum( case days
when 20 then cnt
else 0
end
) day3,
sum( case days
when 25 then cnt
else 0
end
) day4
from
pru t
group by
id
order by id
;

Notice the use of cnt instead of 1 in the then clause. In my earlier
example 1 stands for the fact that you found 1 record that matched the
criteria, when you sum them you have the number of records that you
found. In this example you return cnt which is the number of days of
this type for this ID.