Concatenating column values into a comma-delimited string

Question

Possible Duplicate:
Concat groups in SQL Server

Please have a look at the table structure below:

CREATE TABLE Person (ID INT, Name varchar(30), primary key(ID))
CREATE TABLE Warnings (ID INT, PersonID INT, Description VARCHAR(100), FOREIGN KEY (PersonID) REFERENCES Person(ID))

INSERT INTO Person (1,'Robert')
INSERT INTO Person (2,'Maria')

INSERT INTO Warnings (1,1,'Spitting')
INSERT INTO Warnings (2,1,'Punching')
INSERT INTO Warnings (3,1,'Pinching')

INSERT INTO Warnings (4,2,'Offenive words')
INSERT INTO Warnings (5,2,'Lateness')

I am trying to get an output like this:

Name    Offences
Robert  Spitting,Punching, Pinching
Maria   Offensive words, Lateness

I have looked at pivot tables and the following functions: STUFF and QUOTENAME. I have not yet discovered a solution. The number of offences linked to a person is unknown.

This has been asked many times before, e.g. [Concat groups in SQL Server](http://stackoverflow.com/questions/941103/concat-groups-in-sql-server) or [How to use GROUP BY to concatenate strings in SQL Server?](http://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server) — Pondlife, Jan 08 '13 at 15:58
http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005?lq=1 — lc., Jan 08 '13 at 15:59
Even the simplest search would have turned up many answers to this question... — RBarryYoung, Jan 08 '13 at 16:05

score 3 · Answer 1 · answered Jan 08 '13 at 16:00

SELECT
     a.[Name],
     STUFF(
         (SELECT ',' + [Description]
          FROM Warnings
          WHERE [PersonID] = a.[ID]
          FOR XML PATH (''))
          , 1, 1, '')  AS Offences
FROM Person AS a
     INNER JOIN Warnings b
        ON a.ID = b.PersonID
GROUP BY a.ID,a.[Name]

SQLFiddle Demo

Concatenating column values into a comma-delimited string

1 Answers1