Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums 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 VBS and VB, COBOL syntax is the same ... natt.sut COBOL Programming 3 Sun Nov 12, 2017 6:36 am
No new posts Copy selected columns from 2 input fi... Poha Eater DFSORT/ICETOOL 3 Thu Nov 02, 2017 3:43 pm
No new posts Subtract the ZD fields to provide neg... Balaryan DFSORT/ICETOOL 4 Wed Oct 11, 2017 10:51 pm
No new posts Where is output from PARM ABEXIT (SNA... packerm CA Products 3 Thu Sep 28, 2017 4:06 pm
No new posts Garbage on output file Hervey Martinez SYNCSORT 4 Wed Sep 27, 2017 12:50 am

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