Transpose Output

Author Message
ganeshprasanna

New User

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

Posted: Fri Sep 24, 2010 11:46 am

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

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

All the sql you'll ever need : 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.

publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db29.doc.apsg/db2z_createrecursivesqlcte.htm
www.abis.be/resources/presentations/idug20071106ctedb2.pdf
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) ...
 View Bookmarks All times are GMT + 6 Hours

 Topic Forum Replies Similar Topics Concatenate 2 input datasets and give... JCL & VSAM 2 How to turn off 'ACTION' SDSF output ... TSO/ISPF 2 TRIM everything from input, output co... DFSORT/ICETOOL 1 Sortjoin and Search for a String and ... DFSORT/ICETOOL 1 Joinkeys - 5 output files DFSORT/ICETOOL 7
Search our Forums:

 IBMMainframes.com is not an official and/or affiliated with IBM® in anyway Board Rules | FAQ | Downloads | Wiki | SiteMap | Contact Us