RE: cross tab needs in SQL
Rob asked how to produce a cross-tab (pivot) report in Informix ISQL 7.20 / OnLine 7.23 on HP-UX, where the output columns are dynamic rather than fixed. He was already generating several separate reports and pasting them together, which was slow for larger result sets. Serge Rielau suggested doing the pivot in SQL using MAX(CASE WHEN ...) with GROUP BY (or DECODE), noting that the reverse, column-to-row conversion, needs VALUES/LATERAL which Informix lacks; another poster suggested a Perl script instead. One reply cautioned that CASE/DECODE only appeared in ISQL 7.32 and newer engines, so Rob's old versions may not support it. No confirmation of a working solution is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
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
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
> 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... :(
Serge Wrote:
PIVOT:
SELECT Year,
MAX(CASE WHEN Quarter = 1
THEN Results END) AS Q1,
[delete]
UNPIVOT:
SELECT Year, Quarter, Results
FROM SalesAgg AS S,
[delete]
Cheers
Serge
--
Serge:
Does PIVOT and UNPIVOT work in ISQL 7.2? Rob
Konikoff, Rob (Contractor) wrote:
>
> > 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... :(
> Serge Wrote:
>
> PIVOT:
> SELECT Year,
> MAX(CASE WHEN Quarter = 1
> THEN Results END) AS Q1,> [delete]
>
> UNPIVOT:
> SELECT Year, Quarter, Results
> FROM SalesAgg AS S,> [delete]
>
> Cheers
> Serge
> --
> Serge:
>
> Does PIVOT and UNPIVOT work in ISQL 7.2? Rob
"PIVOT" and "UNPIVOT" are not part of the example.. Just the title.
The usage of MAX and CASE (or DECODE) along with GROUP BY is pretty
standard. It should work in most SQL based engines. Just try it out. ;-)
The other way around (turning columns into rows) requires a way to
construct a table, but VALUES is not very common (I know only of DB2 for
LUW as product that supports it)
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab
This sounds like a job for PERL. Do you have access to it and know it a little? It processes this kind of thing quickly. You can probably find an example on the web. I am no expert (I only do small things in it) but I could cobble a fast enough perl version to flip data the way that you want. I think Jonathan Leffler is an expert on PERL so he might be even better suited to comment on this.
IDS 7.23 and ISQL 7.20 is pretty old CASE and DECODE are in 7.32 of ISQL and the most recent engines