Now we run the code to concatenate the table.

SELECT Team = B.Team,
Runs = STUFF((SELECT ', ' + A.Runs FROM #PlayoffSeries AS A WHERE A.Team = B.Team FOR XML PATH ('')),1,1,'')
FROM #PlayoffSeries AS B

And voila!

Row Concatenation

One T-SQL challenge I've run across many times is how to create a comma delimited string from columnar data. I've done some creative (read: ghetto) things with XML and the replace function but was able to squeeze by because I had only needed to concatenate for a single value. Well, a few weeks ago I had to take an entire table, uniquify the first column and append each corresponding value in the second column as a comma delimited list. Here is my solution.

CREATE TABLE #PlayoffSeries (Inning INT, Team VARCHAR(10), Runs CHAR(1));

INSERT INTO #PlayoffSeries (Inning, Team, Runs)
VALUES (1,'Giants','1'), (2,'Giants','1'), (3,'Giants','5'), (4,'Giants','0'), (5,'Giants','0'), 

(6,'Giants','0'), (7,'Giants','1'), (8,'Giants','1'), (9,'Giants','-'), (1,'Cardinals','0'),
(2,'Cardinals','0'), (3,'Cardinals','0'), (4,'Cardinals','0'), (5,'Cardinals','0'), (6,'Cardinals','0'), 

(7,'Cardinals','0'), (8,'Cardinals','0'), (9,'Cardinals','0');


Once you've created the above temp table, run a simple select to see how the raw data looks.