On 1/5/06, Konikoff, Rob (Contractor) <rob.konikoff@us.army.mil> wrote:
> 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 |


What is not clear to me is whether the columns are fixed at 1, 4, 20,
25, or whether there could, with other data, be columns such as 5, 10,
15, and 95. What happens to the data given on the day after the one
illustrated? Is it now 2, 5, 21, 26, or does the 1 data move to
'2-4', the 4 data might stay put or move to '5-20' (or is it 5-9, and
10-14, and 15-19, and 20-24; or what)?

Without knowing this, it is hard to say what the solution to your
problem is. However, if the columns are not fix, ISQL ACE will have a
hard time dealing with it - unless you write a report generator and
compile the report to deal with the data on hand at the time when you
run it.

In the dim distant past (early '90s), I have done this, and I used
multiple reports and pasted the results together. There was data for
the 'header' (including column labelling), the 'footer' (including
column totals), the left-hand column labels, and the actual column
data (including, optionally, a right-hand column for row totals). I
created 'director' tables containing information about which columns
or rows needed to be included in the output, using outer joins (I
didn't say fast) to make sure that the columns were correctly
formatted, etc. I haven't considered what it would take to adapt that
to the world of HTML. The reports I did to a formatting language
(instead of plain text) were to troff (not recommended these days,
though it is still very powerful), and didn't have to deal with
cross-tabs.


--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/