Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups 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: 1280
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: 1280
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 Problem reading GTF trace output danik56 ABENDS & Debugging 7 Thu Mar 16, 2017 1:02 pm
No new posts SORT VSAM file with each field one by... maxsubrat DFSORT/ICETOOL 6 Tue Mar 14, 2017 1:07 pm
No new posts How to get current date -1 and curren... murali.andaluri DFSORT/ICETOOL 3 Wed Mar 08, 2017 2:09 pm
No new posts Incorrect output when trying to add n... monica1 PL/I & Assembler 10 Fri Jan 13, 2017 5:02 pm
No new posts Problem in writing Output file vickey_dw COBOL Programming 5 Mon Nov 14, 2016 11:14 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us