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
 

 

DB2 query to transpose rows

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

New User


Joined: 03 Aug 2012
Posts: 10
Location: India

PostPosted: Mon Aug 06, 2012 12:37 pm    Post subject: DB2 query to transpose rows
Reply with quote

Please help me in creating DB2 query to transpose rows to one column.
Here is the scenario

Employee table

EMP_SERIAL EMP_NAME
001 John
002 Peter
003 William

Employee shift table

EMPS_SERIAL EMPS_SHIFT EMPS_YRMNTH
001 A 201208
001 C 201208
001 B 201208
002 A 201208
002 C 201208
002 B 201206
003 A 201207


I want to fetch employee and his shift details of current month(here it is 201208).
I want the shift details to be separated by comma and written in a single row.

Result should be like below

EMP_SERIAL EMP_NAME EMPS_SHIFT
001 John A, B, C
002 Peter A, C
Back to top
View user's profile Send private message

gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Mon Aug 06, 2012 1:28 pm    Post subject:
Reply with quote

How many different shifts can be there?
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Mon Aug 06, 2012 1:34 pm    Post subject:
Reply with quote

Why not unload the data and sort it ??
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Mon Aug 06, 2012 1:41 pm    Post subject:
Reply with quote

what vsn of db2?
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Mon Aug 06, 2012 2:57 pm    Post subject:
Reply with quote

http://ibmmainframes.com/about51395.html
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Mon Aug 06, 2012 2:59 pm    Post subject:
Reply with quote

Hi Senthil,

Welcome to IBMMAINFRAMES!

Try the below,
Code:
SELECT                                             
A.EMPS_SERIAL,                                           
EMP_NAME,                                           
REPLACE(                                           
REPLACE(                                           
REPLACE(                                           
CAST(XML2CLOB(XMLAGG(XMLELEMENT(NAME X , EMPS_SHIFT 
             )      )           ) AS VARCHAR(200)) 
,'<X>','')                                         
,'</X>',' ')                                       
,'<X/>',' ') AS EMPS_SHIFT                           
FROM EMPLOYEE_SHIFTS A, EMPLOYEE B
WHERE A.EMPS_SERIAL = B.EMPS_SERIAL                             
GROUP BY A.EMPS_SERIAL, EMP_NAME                         


You could also use the search facility to find other posts of the similar type,
Transpose Output
Transpose the output of an SQL query

Thanks,
Sushanth
Back to top
View user's profile Send private message
Senthil KK

New User


Joined: 03 Aug 2012
Posts: 10
Location: India

PostPosted: Mon Aug 06, 2012 7:28 pm    Post subject: Reply to: DB2 query to transpose rows
Reply with quote

gylbharat - max of 3 shift codes only
Pandora-Box - yes i ve another option to transpose using sort as you said
sushanth bobby - thank you...i will try and get back to you
Back to top
View user's profile Send private message
Senthil KK

New User


Joined: 03 Aug 2012
Posts: 10
Location: India

PostPosted: Mon Aug 06, 2012 8:03 pm    Post subject: Reply to: DB2 query to transpose rows
Reply with quote

Hi Sushanth bobby,

Thanks so much. It works icon_smile.gif
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
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
No new posts Row-Numbers of distinct rows? Auryn DB2 1 Thu Oct 20, 2016 4:38 pm
No new posts Problem in Running Query via JCL vickey_dw DB2 3 Tue Oct 18, 2016 11:11 pm
No new posts SQL Query optimization. arunsoods DB2 12 Sun Sep 04, 2016 6:27 am


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