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: 1534
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: 6967
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: 1281
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
No new posts JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm
No new posts To display values of multiple rows in... vinu78 DB2 2 Thu Mar 09, 2017 1:20 pm
No new posts how to send just 10 rows in a CICS sc... Megha Gupta CICS 5 Thu Feb 23, 2017 6:57 pm
No new posts SQL query not working in Cobol program. CuriousMainframer COBOL Programming 14 Wed Feb 22, 2017 5:56 pm
No new posts Omegamon for DB2 query ashek15 DB2 0 Wed Feb 15, 2017 11:25 am


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