Rows-to-columns question (resend)

Doyle Mark

Rows-to-columns question (resend)
Oops! Missed the inner from clause . . .

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"
From creator.table
where clause(s) if needed
) 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

---------------------------------------------------------------------------------
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

dusan pospisil

Re: Rows-to-columns question
(in response to Doyle Mark)
What about
---------+---------+---------+---------+---------+---------+---------+
SELECT CASE WHEN SUM(MKT_1_)>0 THEN SUM(MKT_1_) ELSE NULL END AS
MKT_1_QUOTE_GRP
, CASE WHEN SUM(MKT_2_)>0 THEN SUM(MKT_2_) ELSE NULL END AS
MKT_2_QUOTE_GRP
, CASE WHEN SUM(MKT_3_)>0 THEN SUM(MKT_3_) ELSE NULL END AS
MKT_3_QUOTE_GRP
FROM (
SELECT QUOTE_GRP MKT_1_,0 MKT_2_,0 MKT_3_,QUOTE_GRP GBY
FROM IDAG WHERE MKT = 1 UNION ALL
SELECT 0 MKT_1_,QUOTE_GRP MKT_2_,0 MKT_3_,QUOTE_GRP GBY
FROM IDAG WHERE MKT = 2 UNION ALL
SELECT 0 MKT_1_,0 MKT_2_,QUOTE_GRP MKT_3_,QUOTE_GRP GBY
FROM IDAG WHERE MKT = 3 ) U1
GROUP BY GBY
ORDER BY GBY
---------+---------+---------+---------+---------+---------+---------+
MKT_1_QUOTE_GRP MKT_2_QUOTE_GRP MKT_3_QUOTE_GRP
---------+---------+---------+---------+---------+---------+---------+
10 10 10
20 20 ---------------
--------------- 30 30
40 --------------- ---------------
--------------- 50 ---------------
--------------- --------------- 60
70 --------------- 70
DSNE610I NUMBER OF ROWS DISPLAYED IS 7
Regard
dp
-----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