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 the output of an SQL query

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

New User


Joined: 26 Jun 2009
Posts: 32
Location: Chennai

PostPosted: Tue Dec 22, 2009 12:47 pm    Post subject: Transpose the output of an SQL query
Reply with quote

I have a select query as follows:
Code:

SELECT COL1,COL2 FROM TABLE
WHERE COL1='X'

The result of this query will be:
Code:

COL1   COL2
X        GI
X        GU
X        LN

My requirement is the query output should be displayed as
Code:

COL1   COL2
X         GI     GU      LN

Can this formatting be done in the query itself?
Back to top
View user's profile Send private message

karisurya

New User


Joined: 02 Aug 2007
Posts: 64
Location: Bangalore

PostPosted: Tue Dec 22, 2009 2:29 pm    Post subject:
Reply with quote

Hi Arun,

I am not sure can it be done or not but just curious why you want to do that?
Is it a exercise or some project requirement? A bit more back ground and clear explanation why you want to do that will help you to get quick replies.

Regards,
Surya
Back to top
View user's profile Send private message
r arunmoezhi

New User


Joined: 26 Jun 2009
Posts: 32
Location: Chennai

PostPosted: Tue Dec 22, 2009 2:43 pm    Post subject:
Reply with quote

It is a project requirement.
The current query returns multiple records. But if this query is changed so that it returns only one record i can use that record to insert into another table which has only two columns. And the primary key for the second table is col1. So it cannot have duplicates.
Back to top
View user's profile Send private message
Srihari Gonugunta

Active User


Joined: 14 Sep 2007
Posts: 295
Location: Singapore

PostPosted: Tue Dec 22, 2009 4:11 pm    Post subject:
Reply with quote

Arun,
You can try this. This is tested one. Hope it works for you.

Code:
WITH TRANCTE(COL1,COL2,AUX) AS
(SELECT COL1,CAST(COL2 AS VARCHAR(255)),CAST(NULL AS VARCHAR(255))
FROM YOUR_TABLE WHERE COL1 = 'X'
UNION ALL
SELECT A.COL1,A.COL2||' '||B.COL2,B.COL2
FROM TRANCTE A , YOUR_TABLE B
 WHERE  A.COL1 = B.COL1 AND
        LOCATE(B.COL2,A.COL2) = 0 AND
        COALESCE(A.AUX,' ') < B.COL2
)
SELECT TX.COL1, TX.COL2
FROM   TRANCTE AS TX
WHERE  LENGTH(TX.COL2) = (SELECT MAX(LENGTH(T.COL2))
                    FROM   TRANCTE AS T
                    WHERE  T.COL1 = TX.COL1)
FETCH FIRST 1 ROWS ONLY;
Back to top
View user's profile Send private message
r arunmoezhi

New User


Joined: 26 Jun 2009
Posts: 32
Location: Chennai

PostPosted: Tue Dec 22, 2009 5:17 pm    Post subject:
Reply with quote

worked perfectly. icon_smile.gif thanks a lot.
can you please post an explanation of this query. especially with respect to LOCATE.
Back to top
View user's profile Send private message
karisurya

New User


Joined: 02 Aug 2007
Posts: 64
Location: Bangalore

PostPosted: Tue Dec 22, 2009 5:27 pm    Post subject:
Reply with quote

Hi Srihari,

Really good info and working perfect for me when tested.

I am realtively new to Recursive sql can you please explain the query or please guide me where i can find material on this.

Regards,
Surya
Back to top
View user's profile Send private message
Srihari Gonugunta

Active User


Joined: 14 Sep 2007
Posts: 295
Location: Singapore

PostPosted: Tue Dec 22, 2009 5:44 pm    Post subject:
Reply with quote

Hi,

Please find the detailed explanation of recursive SQL in the following link.

http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/DSNAPJ10/APPENDIX1.5?DT=20040210163115#HDRQAPPENZ

I used LOCATE function to avoid concatenation of duplicate values.

Code:
WITH TRANCTE(COL1,COL2,AUX) AS
(SELECT COL1,CAST(COL2 AS VARCHAR(255)),CAST(NULL AS VARCHAR(255))
FROM YOUR_TABLE WHERE COL1 = 'X'
UNION ALL
SELECT A.COL1,A.COL2||' '||B.COL2,B.COL2
FROM TRANCTE A , YOUR_TABLE B
 WHERE  A.COL1 = B.COL1 AND
        LOCATE(B.COL2,A.COL2) = 0 AND
        COALESCE(A.AUX,' ') < B.COL2
)
SELECT * FROM   TRANCTE;


Check this out for all the rows generated by the above query. That gives better understanding.
Back to top
View user's profile Send private message
karisurya

New User


Joined: 02 Aug 2007
Posts: 64
Location: Bangalore

PostPosted: Tue Dec 22, 2009 5:55 pm    Post subject:
Reply with quote

Hi Srihari,

Thank you for the info.

Regards,
Surya
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 Join in SQL Query vickey_dw DB2 2 Tue Jan 17, 2017 12:15 am
No new posts Incorrect output when trying to add n... monica1 PL/I & Assembler 10 Fri Jan 13, 2017 5:02 pm
This topic is locked: you cannot edit posts or make replies. MQ setup query mohitsaini Java & MQSeries 4 Sun Jan 08, 2017 1:53 am
No new posts DB2 query Using Recursion, Converting... smilewithashu2 DB2 1 Tue Jan 03, 2017 12:50 pm
No new posts SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am


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