Combine into one Row: Select with Case() and Group BY
523220Mar 9 2011 — edited Mar 9 2011Hi,
I have a small Problem and it seems my knowledge and expirience is just not good enough to slove it.
To better understand my question here an example (the actual query is too big):
Data:
NAME, CASE_NO, GO_TAG
Tom 10 go
Tom 15 go
Tom 20
Tim 30
Tim 40
My Query looks like this:
SELECT NAME,
CASE
WHEN (GO_TAG IS NOT NULL) THEN wm_concat(CASE_NO)
ELSE NULL
END WITH_GO_TAG,
CASE
WHEN (GO_TAG IS NULL) THEN wm_concat(CASE_NO)
ELSE NULL
END WITHOUT_GO_TAG
GROUP BY NAME, GO_TAG;
Now what i get as output:
NAME, WITH_GO_TAG, WITHOUT_GO_TAG
Tom, '10, 15', ''
Tom, '', '20'
Tom, '10, 15', ''
Tom, '', '30, 40'
Here is my Problem: I want one row instead of two rows for "Tom".
So it looks like: Tom, '10, 15', '20'
Is this possible ?
Be aware i coded this SQL Stuff right directly into this forum to show you my Problem... hope you can understand it :-)
Hope anyone can help me in this case.
oh, almost forgot, I (or better: my company) use Oracle Version: 10g
Cheers,
Phil