View previous topic :: View next topic
|
Author |
Message |
vinu78
Active User
Joined: 02 Oct 2008 Posts: 179 Location: India
|
|
|
|
Hi All,
My requirement is, to have values in multiple rows of a query to be displayed in single row in different column fields.
DB2 Query
Code: |
SELECT A.PROV_ID, A.SEQ_NO,B.SPEC_CD,
FROM TABLEA A,
TABLEB B
WHERE A.PROV_ID = '12345'
AND A.SPEC_CD= B.SPEC_CD
ORDER BY A.SEQ_NUM |
Code: |
Current O/p
PROV_ID SEQ_NO SPEC_CD
12345 1 AB
12345 2 CD
12345 3 EF |
Code: |
Expected Output
PROV_ID SEQ_NO SPEC_1 SPEC_2
111 1 AB CD |
If we get single row as output, the expected ouput is
Code: |
PROV_ID SEQ_NO SPEC_1 SPEC_2
111 1 AB <blanks> |
I have tried using LISTAGG function but is not working out.
Please help.
Thanks
Vinu |
|
Back to top |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007 Posts: 2455 Location: Hampshire, UK
|
|
|
|
Someone is sure to ask:
What happened to the EF from the third row?
Why has PROV_ID changed from 12345 to 111? |
|
Back to top |
|
|
vinu78
Active User
Joined: 02 Oct 2008 Posts: 179 Location: India
|
|
|
|
Oh thanks Nic for pointing out that.
Sorry all.
PROV_ID is 12345 only and not 111(sorry, typo mistake)
I just want the first 2 row values to be in column fields and can ignore the third row value onwards. |
|
Back to top |
|
|
|