|
View previous topic :: View next topic
|
| Author |
Message |
Senthil KK
New User
Joined: 03 Aug 2012
Posts: 10
Location: India
|
|
|
|
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 |
|
 |
gylbharat
Active Member
.jpg)
Joined: 31 Jul 2009
Posts: 565
Location: Bangalore
|
|
|
|
| How many different shifts can be there? |
|
| Back to top |
|
|
Pandora-Box
Global Moderator
.jpg)
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
| Why not unload the data and sort it ?? |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
| what vsn of db2? |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
| Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
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 |
|
|
Senthil KK
New User
Joined: 03 Aug 2012
Posts: 10
Location: India
|
|
|
|
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 |
|
|
Senthil KK
New User
Joined: 03 Aug 2012
Posts: 10
Location: India
|
|
|
|
Hi Sushanth bobby,
Thanks so much. It works  |
|
| Back to top |
|
|
|
|
