Posted by jhofmeyr on 11/23/07 11:52

Hi Bart,

I've seen this question answered very neatly before, so with a bit of
digging and some copy/paste I came up with:

CREATE TABLE test (test1 VARCHAR(5), test2 varchar(5), test3
varchar(5))

INSERT INTO test(test1, test2, test3)
SELECT 'A', '1', 'C'
UNION ALL
SELECT 'A', '2', 'C'
UNION ALL
SELECT 'A', '3', 'C'
UNION ALL
SELECT 'B', '8', 'C'
UNION ALL
SELECT 'B', '9', 'C'

SELECT test1, SUBSTRING((select ', ' + test2 as [text()]
from test t
where t.test1 = ot.test1
for xml path(''), elements), 3, 100) as test2, test3
FROM test ot
GROUP BY test1, test3

DROP TABLE test

which seems to work :)

Good luck!
J

[Back to original message]