resultset left to right as opposed to top to bottom 1

[Igaduma]

Hello All,

I'm breaking my head over this problem I got,
Vaguely I remember how I once solved this, but sadly I forgot and find myself at the end of my options..
I need to mold a resultset into another format.

This is the format the table is in:

COL1 COL2
---------
0010 A
0010 B
0010 C
0020 A
0020 B
0030 A
0040 A

The actual layout should be like this:

COL1 COL2
---------
0010 A,B,C
0020 A,B
0030 A
0040 A

So, instead of having multiple instances of COL1 from top to bottom I should end up with a resultset that only has 1 occurence of COL1 and then lists all values it came accros in the table in COL2.

Any help / nifty sql is -more- then welcome at this point.

Thanks!

Iga

 

[LKBrwnDBA]

This question has been asked and answered thousands of time in this and other forums.

Please do some research before posting.

Hint: Search for 'pivot table' or 'crosstab'.



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb