Rows-to-columns question

Dave Schaeffer

Rows-to-columns question
Hello everyone,

I've been messing around with some SQL and was hoping to get some
ideas on
how to write this. The environment is - DB2 v7.1 on z/OS What I'm
trying to do
is take a table that is in rows and report it in columns, below is a
sample of
the data.

MKT QUOTE_GRP
--- ---------
1 10
2 10
3 10
1 20
2 20
2 30
3 30
1 40
2 50
3 60
1 70
3 70
etc.......


The results that we are looking for:

MKT_1_QUOTE_GRP MKT_2_QUOTE_GRP MKT_3_QUOTE_GRP
--------------- --------------- ---------------
10 10 10
20 20 NULL
NULL 30 30
40 NULL NULL
NULL 50 NULL
NULL NULL 60
70 NULL 70

Does anyone have an idea on how to construct the SQL to produce this
results?



Dave Schaeffer

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Sorensen Henrik (KAFC 21)

Re: Rows-to-columns question
(in response to Dave Schaeffer)
Hi,

I wrote a simple table function, PIVOT, that takes and sql as parameter,
and returns a table with the rows as columns.

select *
from PIVOT("select ID,COL from mytable order by ID")

If sql returns:
Row ! ID ! COL !
1 ! A ! 12/03 !
2 ! A ! 05/06 !
3 ! A ! 18/08 !
4 ! B ! 01/01 !
5 ! B ! 07/09 !
6 ! C ! 24/12 !

PIVOT(sql) returns one row pr value of ID:
Row ! N ! ID ! C01 ! C02 ! C03 !
1 ! 3 ! A ! 12/03 ! 05/06 ! 18/08 !
2 ! 2 ! B ! 01/01 ! 07/09 ! - !
3 ! 1 ! C ! 24/12 ! - ! - !


it works quiet neat.

You have to decide how many columns you want to have pr. ID. I settled
for 31, because I needed it for days within a month.

hope this helps
Henrik



-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of [login to unmask email]
Sent: Thursday, January 18, 2007 8:17 PM
To: [login to unmask email]
Subject: [DB2-L] Rows-to-columns question

Hello everyone,

I've been messing around with some SQL and was hoping to get some
ideas on
how to write this. The environment is - DB2 v7.1 on z/OS What I'm
trying to do
is take a table that is in rows and report it in columns, below is a
sample of
the data.

MKT QUOTE_GRP
--- ---------
1 10
2 10
3 10
1 20
2 20
2 30
3 30
1 40
2 50
3 60
1 70
3 70
etc.......


The results that we are looking for:

MKT_1_QUOTE_GRP MKT_2_QUOTE_GRP MKT_3_QUOTE_GRP
--------------- --------------- ---------------
10 10 10
20 20 NULL
NULL 30 30
40 NULL NULL
NULL 50 NULL
NULL NULL 60
70 NULL 70

Does anyone have an idea on how to construct the SQL to produce this
results?



Dave Schaeffer

------------------------------------------------------------------------
---------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Doyle Mark

Re: Rows-to-columns question
(in response to Sorensen Henrik (KAFC 21))
If you know how many MKTs (and the values) you have, I expect you can
use this kind of construct:

Select case when sum(a.first) > 0 then quote_grp else null end as
"MKT_1_QUOTE_GRP" ,
case when sum(a.second) > 0 then quote_grp else null end as
"MKT_2_QUOTE_GRP",
case when sum(a.third) > 0 then quote_grp else null end as
"MKT_3_QUOTE_GRP",
etc.
case when sum(a.last) > 0 then quote_grp else null end as
"MKT_x_QUOTE_GRP",
From (
Select quote_grp,
case when mkt=1 then 1 else 0 end as "first",
case when mkt=2 then 1 else 0 end as "second",
case when mkt=3 then 1 else 0 end as "third",
etc
case when mkt= last then 1 else 0 end as "last"
) as a
Group by a.quote_grp

YMMV, not tested, but should work in V7

Mark

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of [login to unmask email]
Sent: Thursday, January 18, 2007 1:17 PM
To: [login to unmask email]
Subject: Rows-to-columns question


Hello everyone,

I've been messing around with some SQL and was hoping to get some
ideas on how to write this. The environment is - DB2 v7.1 on z/OS What
I'm trying to do is take a table that is in rows and report it in
columns, below is a sample of the data.

MKT QUOTE_GRP
--- ---------
1 10
2 10
3 10
1 20
2 20
2 30
3 30
1 40
2 50
3 60
1 70
3 70
etc.......


The results that we are looking for:

MKT_1_QUOTE_GRP MKT_2_QUOTE_GRP MKT_3_QUOTE_GRP
--------------- --------------- ---------------
10 10 10
20 20 NULL
NULL 30 30
40 NULL NULL
NULL 50 NULL
NULL NULL 60
70 NULL 70

Does anyone have an idea on how to construct the SQL to produce this
results?



Dave Schaeffer

------------------------------------------------------------------------
---------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm