SEO

#1 (**permalink**) 04-20-2008, 10:04 AM

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

#2 (**permalink**) 04-20-2008, 10:04 AM

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

#3 (**permalink**) 04-20-2008, 10:05 AM

> 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

#4 (**permalink**) 04-20-2008, 10:05 AM

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

#5 (**permalink**) 04-20-2008, 10:05 AM

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.

#6 (**permalink**) 04-20-2008, 10:05 AM

IDS 7.23 and ISQL 7.20 is pretty old

CASE and DECODE are in 7.32 of ISQL and the most recent engines

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

RE: cross tab needs in SQL