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
 
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: 1544
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: 6970
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 reg query on DYNALLOC feature raghuraman123 SYNCSORT 12 Wed Jan 10, 2018 2:42 pm
No new posts query to fetch record which has only ... maxsubrat DB2 12 Mon Dec 11, 2017 5:03 pm
No new posts Merging 2 records at multiple rows wi... Bijesh DFSORT/ICETOOL 2 Wed Dec 06, 2017 1:50 am
No new posts Can we combine the rows with same key... V S Amarendra Reddy DB2 12 Fri Dec 01, 2017 10:29 pm
No new posts How to query MAXGENS of an existing P... Chuchulo Hatyzak JCL & VSAM 4 Wed Nov 22, 2017 10:23 pm

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