Konikoff, Rob (Contractor) wrote:
> Jonathan:
>
> 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?
> > What I need:
> > ID|1 Day|4 Day |20 Day|25 Day|
> > 01| 25 | 20 | | |
> - What is not clear to me is whether the columns are fixed at
>
> They are dynamic...
>
> - However, if the columns are not fix, ISQL ACE will have a
> hard time dealing with it
>
>
>
> Yes... it has a severe tummy ache (and I get a head ache) when I try to
> get it to handle this kind of problem.
>
>
>
> - In the dim distant past (early '90s), I have done this, and I used
> multiple reports and pasted the results
>
> That's what I'm doing now with most of my reports like this. It is some
> what awkward and slow (only useful for small reports). I'm thinking of
> working up something in awk, but frankly, I was hoping to be able to do
> this in the database engine. Once the data length exceeds a few hundred
> lines, the UNIX run time for the cobbling together is as long as the SQL
> run time.
>
> Thanks for the feedback.
>
> Rob Konikoff
Don't know iSQL, but here is the densest version I can think of in
ANSI (simple enough to be glued together by a generator):
CREATE TABLE SalesAgg(year INTEGER,
q1 INTEGER,
q2 INTEGER,
q3 INTEGER,
q4 INTEGER);

CREATE TABLE Sales(Year INTEGER,
Quarter INTEGER,
Results INTEGER);

PIVOT:
SELECT Year,
MAX(CASE WHEN Quarter = 1
THEN Results END) AS Q1,
MAX(CASE WHEN Quarter = 2
THEN Results END) AS Q2,
MAX(CASE WHEN Quarter = 3
THEN Results END) AS Q3,
MAX(CASE WHEN Quarter = 4
THEN Results END) AS Q4
FROM Sales
GROUP BY Year

Note: MAX() works on all comparable datatypes...

UNPIVOT:
SELECT Year, Quarter, Results
FROM SalesAgg AS S,
LATERAL(VALUES(1, S.q1),
(2, S.q2),
(3, S.q3),
(4, S.q4))
AS Q(Quarter, Results);

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab