Concatenating column values into a comma-delimited string [duplicate]

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.

Answer 1

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