Try this, should help you to solve your request.
DemoTable:
CREATE TABLE Demo
(
DPS CHAR(30),
SKILL CHAR(30)
);
Test Data:
INSERT INTO Demo (DPS, SKILL) VALUES
('X', 'A'),
('X', 'B'),
('Y', 'C'),
('Y', 'D');
Test SQL:
SELECT DPS,
SKILLVALS= STUFF((SELECT RTRIM(SKILL) + ','
FROM Demo D2
WHERE D2.DPS = D1.DPS
ORDER BY D2.SKILL
FOR XML PATH('')), 1, 0, '')
FROM Demo D1
GROUP BY D1.DPS
ORDER BY D1.DPSS
Test Result:
DPS SKILLVALS
--- ---------
X A,B,
Y C,D,
Small drawback: The ',' at the end of SKILLVALS.
And no, I did not find this solution by studying the MSDN documentation :)
Here you find the information:
SQL Server Grouped Concatenation - SQLPerformance.com[
^], search for 'FOR XML PATH' there.
I hope it helps.
[Edit] Thanks to
Richard Deeming - Professional Profile[
^], see his comment to the above solution.
SELECT DPS,
SKILLVALS= STUFF((SELECT ',' + RTRIM(SKILL)
FROM Demo D2
WHERE D2.DPS = D1.DPS
ORDER BY D2.SKILL
FOR XML PATH('')), 1, 1, '')
FROM Demo D1
GROUP BY D1.DPS
ORDER BY D1.DPS
Finally the result without leading or trailing ',':
DPS SKILLVALS
--- ---------
X A,B
Y C,D