Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref

Author Message
ganeshprasanna

New User

Joined: 21 Feb 2009
Posts: 36
Location: South Portland, Maine

Posted: Fri Sep 24, 2010 11:46 am    Post subject: Transpose Output

SQL query :

 Code: SELECT A,B,C FROM TABLE WHERE A=6990

Result set:
 Code: A                B            C  DECIMAL(14)      CHAR(1)      SMALLINT(2) -PKEY4---------  -PKEY2-----  -PKEY3-----                                           +6990            B            1          +6990            B            2          +6990            B            4          +6990            B            5          +6990            C            1          +6990            C            2          +6990            C            3          +6990            C            4          +6990            C            5          +6990            C            6          +6990            C            7

PS: The result set (no of rows ) returned might vary with the value of A..

My requirement is the query output should be displayed as

 Code: A                B            C                                            +6990            B            1     2      4    5      +6990            C            1     2      3    4    5    6    7

Can this formatting be done in the query itself?

Help on this would be appreciated ..

Thanks,
Ganeshprasanna

GuyC

Senior Member

Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

Posted: Fri Sep 24, 2010 2:03 pm    Post subject:

DB2 version ?
Using recursive SQL (this works as long as C is unique within cola,colB)
 Code: with cte (cnt,colA, colB,colC ,outp) as  ( select 1, colA, colB, min(colC), cast(char(min(colC)) as varchar(200))      from tab1     where colA = +6990     group by colA,colB   union all    select a.cnt + 1 , a.colA, a.colB, b.colC, a.outp || char(b.colC)    from cte A , tab1 B    where a.colA = b.colA and a.colB = b.colB    and b.colC = (select min(colC) from tab1 C where c.colC > a.colC)    and a.cnt < 100 ) select colA,colB,outp from cte A where cnt = (select max(cnt) from cte B where a.colA = b.colA and a.colB = b.colB)

Using XMLFunctions

 Code: select colA, colB , replace(replace(cast(xml2clob(xmlagg(xmlelement(name X ,colC))) as varchar(200) ),'',' ') ,'','') from  tab1 group by colA,colB
ganeshprasanna

New User

Joined: 21 Feb 2009
Posts: 36
Location: South Portland, Maine

 Posted: Fri Sep 24, 2010 3:26 pm    Post subject: DB2 version - 9 Worked like a charm, GUYC. Any pointers to learning recursive SQL usage. Examples or illustrations would be helpful. Thanks, Ganeshprasanna
GuyC

Senior Member

Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

Posted: Fri Sep 24, 2010 3:44 pm    Post subject:

All the sql you'll ever need : http://mysite.verizon.net/Graeme_Birchall/cookbook/DB2V95CK.PDF has a whole chapter on recursion.
Some stuff in here is not for z/OS, but most of it is, or will be in DB2 10.

http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db29.doc.apsg/db2z_createrecursivesqlcte.htm
http://www.abis.be/resources/presentations/idug20071106ctedb2.pdf
http://www.craigsmullins.com/bp5.htm

Just noticed that I'm missing a join condition in my first SQL:
replace with :
 Code: ... and b.colC = (select min(colC) from tab1 C where c.colC > a.colC and a.colA = c.colA and a.colB = c.colB) ...
 All times are GMT + 6 Hours
 Page 1 of 1

Search our Forum:

 Topic Author Forum Replies Posted Similar Topics SDSF REXX for DB2 command output vasanthz CLIST & REXX 2 Thu Apr 25, 2019 11:29 pm SDSF limit access to specifc output c... David Beckham All Other Mainframe Topics 1 Thu Mar 21, 2019 10:29 pm Build Dynamic Sort output and limit '... balaji81_k DFSORT/ICETOOL 3 Sat Feb 02, 2019 4:40 am Copy members from Input PDS to Output... Poha Eater JCL & VSAM 9 Tue Jan 29, 2019 3:56 pm DFSORT output in CSV for data spans i... hiravibk DFSORT/ICETOOL 3 Tue Nov 13, 2018 7:55 pm

 © 2003-2017 IBM MAINFRAME Software Support Division
 Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us