STUFF in T-SQL will let you flatten the values across numerous rows (for one column) into a comma-separated string!

I really don't understand how to use STUFF yet myself.

 
 

Addendum 10/25/2015: Here is an example of STUFF:

IF OBJECT_ID('tempdb..#Temp') IS NOT NULL DROP TABLE #Temp
SELECT b.RecruiterId, t.Name
INTO #Temp
FROM Bridge b
JOIN Techies t
ON b.TechId = t.TechId
SELECT r.Name AS Recruiter,
STUFF((SELECT ', ' + Name
   FROM #Temp t
   WHERE r.RecruiterId = t.RecruiterId
   FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)')
   , 1, 2, '')
AS Prospects
FROM Recruiters r

 
 

This SQL will give us a report of which candidates recruiters are talking to. (It's a silly example.) There are separate tables for recruiters and the tech people they court. There is also a bridge table called bridge for managing the many-to-many relationship. The tables and their contents look like this:

 
 

In my query I pull a join between the bridge table and the candidates into a temp table and then stuff the temp tables contents into concatenated strings with comma separations between candidate names like so:

 
 

In case you are curious, I used the following SQL to set up the three tables:

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Recruiters
   (
   RecruiterId int NOT NULL IDENTITY (1, 1),
   Name varchar(8) NOT NULL
   ) ON [PRIMARY]
GO
ALTER TABLE dbo.Recruiters ADD CONSTRAINT
   PK_Recruiters PRIMARY KEY CLUSTERED
   (
   RecruiterId
   ) WITH( STATISTICS_NORECOMPUTE = OFF,
   IGNORE_DUP_KEY = OFF,
   ALLOW_ROW_LOCKS = ON,
   ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE dbo.Recruiters SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
SET IDENTITY_INSERT [dbo].[Recruiters] ON
INSERT INTO Recruiters (RecruiterId, Name) VALUES (1,'Heather')
INSERT INTO Recruiters (RecruiterId, Name) VALUES (2,'Ingrid')
INSERT INTO Recruiters (RecruiterId, Name) VALUES (3,'Jennifer')
INSERT INTO Recruiters (RecruiterId, Name) VALUES (4,'Kristy')
SET IDENTITY_INSERT [dbo].[Recruiters] OFF
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Techies
   (
   TechId int NOT NULL IDENTITY (1, 1),
   Name varchar(6) NOT NULL
   ) ON [PRIMARY]
GO
ALTER TABLE dbo.Techies ADD CONSTRAINT
   PK_Techies PRIMARY KEY CLUSTERED
   (
   TechId
   ) WITH( STATISTICS_NORECOMPUTE = OFF,
   IGNORE_DUP_KEY = OFF,
   ALLOW_ROW_LOCKS = ON,
   ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE dbo.Techies SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
SET IDENTITY_INSERT [dbo].[Techies] ON
INSERT INTO Techies (TechId, Name) VALUES (1,'Larry')
INSERT INTO Techies (TechId, Name) VALUES (2,'Marty')
INSERT INTO Techies (TechId, Name) VALUES (3,'Norman')
INSERT INTO Techies (TechId, Name) VALUES (4,'Oliver')
INSERT INTO Techies (TechId, Name) VALUES (5,'Pete')
SET IDENTITY_INSERT [dbo].[Techies] OFF
BEGIN
CREATE TABLE dbo.Bridge
(
   BridgeId int IDENTITY NOT NULL,
   RecruiterId int NOT NULL,
   TechId int NOT NULL
) ON [PRIMARY]
ALTER TABLE dbo.Bridge ADD CONSTRAINT
   PK_Bridge PRIMARY KEY CLUSTERED
   (
   BridgeId
   ) WITH (
   STATISTICS_NORECOMPUTE = OFF,
   IGNORE_DUP_KEY = OFF,
   ALLOW_ROW_LOCKS = ON,
   ALLOW_PAGE_LOCKS = ON
   ) ON [PRIMARY]
ALTER TABLE dbo.Bridge ADD CONSTRAINT
   FK_Bridge_To_Recruiters FOREIGN KEY
   (
   RecruiterId
   ) REFERENCES dbo.Recruiters (
   RecruiterId
   ) ON UPDATE NO ACTION ON DELETE NO ACTION
ALTER TABLE dbo.Bridge ADD CONSTRAINT
   FK_Bridge_To_Techies FOREIGN KEY
   (
   TechId
   ) REFERENCES dbo.Techies (
   TechId
   ) ON UPDATE NO ACTION ON DELETE NO ACTION
END
GO
INSERT INTO Bridge (RecruiterId, TechId) VALUES (1, 1)
INSERT INTO Bridge (RecruiterId, TechId) VALUES (1, 2)
INSERT INTO Bridge (RecruiterId, TechId) VALUES (1, 3)
INSERT INTO Bridge (RecruiterId, TechId) VALUES (2, 2)
INSERT INTO Bridge (RecruiterId, TechId) VALUES (2, 3)
INSERT INTO Bridge (RecruiterId, TechId) VALUES (2, 4)
INSERT INTO Bridge (RecruiterId, TechId) VALUES (2, 5)
INSERT INTO Bridge (RecruiterId, TechId) VALUES (3, 1)
INSERT INTO Bridge (RecruiterId, TechId) VALUES (3, 3)
INSERT INTO Bridge (RecruiterId, TechId) VALUES (3, 5)
INSERT INTO Bridge (RecruiterId, TechId) VALUES (4, 1)
INSERT INTO Bridge (RecruiterId, TechId) VALUES (4, 2)
INSERT INTO Bridge (RecruiterId, TechId) VALUES (4, 4)

 
 

I'm glad I keep such good notes. I used the following as a crutch I crafting what is immediately above:

1. http://tom-jaeschke.blogspot.com/2013/03/insert-into-numeric-primary-key-column.html
2. http://tom-jaeschke.blogspot.com/2015/06/the-classic-mssql-bridge-table.html
3. http://tom-jaeschke.blogspot.com/2015/09/cursors-are-ghetto-just-select-directly.html