Comments (4)
  • Add a Comment
  • Edit
  • More Actions v
  • Quarantine this Entry

1 Peter.S commented Permalink

Hello Serge, Very useful SQL and I am trying to use it to return attribute, value pairs,

 
select tabschema, tabname, col1, value1 from syscat.tables as s,
lateral ( values ('COLCOUNT', s.colcount),
('PARENTS', s.parents ),
('TBSPACE', s.TBSPACE)
) AS T( col1, value1)
where tabname = 'SYSTABLES';
 
LATERAL works well as long as the attributes are numeric. Do you have a method to unpivot a table to return something like the following:
 
Tabschema, tabname, colname, value
 
Regards, Peter Schwarcz

2 cjayaram commented Permalink

Hi Surge,
Please help me on the following requirement:
I have a table with the following data:

 
TABLEA
CLI_ID ADR_TYP ADDRS CHNG_DATE PHONE
===================================
A1 PR HYD 2010-01-01 123344
A1 PR MAS 2010-05-01 123986
A1 M HYD1 2010-02-01 1234465
A1 M HYD2 2010-01-01 1234467
 
I want to see the result data as follows:
CLI_ID PR_ADR_TYP ADDR PR_CHNG_DATE M_ADR_TYP M_CHNG_DATE
========================================================
A1 PR MAS 2010-05-01 M 2010-02-01
 
Note: the date is latest date for the each ADR_TYP.
 
Please help me how to achieve this using DB2 SQL.

3 SergeRielau commented Permalink

Jayaram,

 
Thsi is a combination of two things PIVOT/UNPIVOT and a prefiltering of the values to get the latest row/group.
So you need to start here:
https://www.ibm.com/developerworks/mydeveloperworks/blogs/SQLTips4DB2LUW/entry/finding_the_maximum_row_and_more56?lang=en
 
and ten apply pivoting to the result.
 
Cheers
Serge

4 Jim_IT commented Permalink

Serge,

 
I like your approach using lateral correlation in UNPIVOT, but why not use the same approach in PIVOT?
 
Select
YEAR,
Max(Q1) As Q1,
Max(Q2) As Q2,
Max(Q3) As Q3,
Max(Q4) As Q4
From
Sales
Left Join Lateral
(Values
(1,Results,0,0,0),
(2,0,Results,0,0),
(3,0,0,Results,0),
(4,0,0,0,Results)) As B (QTR, Q1, Q2, Q3, Q4)
On
Quarter = QTR
Group By
YEAR;
 
 
Jim