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 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 Subtract the ZD fields to provide neg... Balaryan DFSORT/ICETOOL 4 Wed Oct 11, 2017 10:51 pm
No new posts HEX value search in a DB2 query maxsubrat DB2 2 Wed Oct 04, 2017 3:04 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
No new posts it is possible output a SET value fro... Martylin JCL & VSAM 5 Fri Sep 22, 2017 7:08 am

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