IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Transpose Output


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
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
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
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
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
Reply with quote

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.

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

 


Similar Topics
Topic Forum Replies
No new posts Altering output file attributes All Other Mainframe Topics 5
No new posts DB2 - row_number function - Need 1st ... DB2 5
No new posts Write 9(07) comp-3 data into output file DFSORT/ICETOOL 4
No new posts DFSORT Output file order query DFSORT/ICETOOL 2
No new posts Need a Macro to compare datasets and ... All Other Mainframe Topics 2
Search our Forums:

Back to Top