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
;

This perl program should do the trick and it should be fast:
Of course substitute your path to perl after #! on first line. From vi
I always r! which perl
..
You call it like
crosstab.pl <filename>

#!/usr/bin/perl -w

use strict;

my %htCols ;
my %htData ;
my( $id, $days, $count);
while (<>) {
chomp;
($id, $days, $count) = split /\|/ ;
$htCols{ $days } = 1 ;
$htData{ $id }{ $days } = $count ;
}

my @colKeys = sort keys %htCols ;

foreach my $id (sort keys %htData) {
printf("$id|");
foreach my $col (@colKeys) {
if (exists($htData{$id}{$col}) ) {
printf("%s|",$htData{$id}{$col})
}
else {
print "|" ;
}
}
print "\n" ;
}