Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Forum Index
 
Register
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Transpose Output

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
ganeshprasanna

New User


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

PostPosted: Fri Sep 24, 2010 11:46 am    Post subject: Transpose Output
Reply with quote

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
Back to top
View user's profile Send private message

GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Fri Sep 24, 2010 2:03 pm    Post subject:
Reply with quote

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) ),'<X>',' ') ,'</X>','')
from  tab1
group by colA,colB
Back to top
View user's profile Send private message
ganeshprasanna

New User


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

PostPosted: Fri Sep 24, 2010 3:26 pm    Post subject:
Reply with quote

DB2 version - 9

Worked like a charm, GUYC. icon_smile.gif
Any pointers to learning recursive SQL usage. Examples or illustrations would be helpful.


Thanks,
Ganeshprasanna
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Fri Sep 24, 2010 3:44 pm    Post subject:
Reply with quote

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.

other stuff from google:
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)
...
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

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

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us