Selecting Multiple Rows as a Single Column in SQL


Recently, I was writing a report that selected some data out of a normalized SQL Server database. For one of the queries, I needed a “flattened” form of the rows in one of the tables. In essence, what I needed to do was turn several rows of data into a single column.

[more]

Here’s an example of what I was looking at:

The Data Tables

ParentId FirstName LastName Age
1 John Smith 26
2 Jane Johnson 32
3 Jacob Doe 29
ChildId ParentId FullName
1 1 Johnny Smith
2 2 Billy Johnson
3 2 Samantha Johnson
4 3 Megan Doe
5 3 Willy Doe
6 3 Bobby Doe

I’ll setup this example like this:

DECLARE @Parents AS TABLE (
	ParentId INT PRIMARY KEY,
	FirstName VARCHAR(50),
	LastName VARCHAR(50), Age INT)
DECLARE @Children AS TABLE (
	ChildId INT PRIMARY KEY,
	ParentId INT, FullName VARCHAR(50))

INSERT INTO @Parents VALUES (1, 'John', 'Smith', 26)
INSERT INTO @Parents VALUES (2, 'Jane', 'Johnson', 32)
INSERT INTO @Parents VALUES (3, 'Jacob', 'Doe', 29)

INSERT INTO @Children VALUES (1, 1, 'Jonny Smith')
INSERT INTO @Children VALUES (2, 2, 'Billy Johnson')
INSERT INTO @Children VALUES (3, 2, 'Samantha Johnson')
INSERT INTO @Children VALUES (4, 3, 'Megan Doe')
INSERT INTO @Children VALUES (5, 3, 'Willy Doe')
INSERT INTO @Children VALUES (6, 3, 'Bobby Doe')

The Desired Output

What I needed was a table of the parents along with a column that contained a comma-delimited list of each parent’s children. Something like this:

ParentId FirstName LastName Age Children
1 John Smith 26 Jonny Smith
2 Jane Johnson 32 Billy Johnson, Samantha Johnson
3 Jacob Doe 29 Megan Doe, Willy Doe, Bobby Doe

The Solution

The “secret sauce” for the solution involves the STUFF Function and the FOR XML PATH statement. You’ll notice that if we select from the child table using FOR XML PATH(‘’):

SELECT ', ' + c.FullName
FROM @Children c
WHERE c.ParentId=1
FOR XML PATH('')

The output looks like:

XML_F52E2B61-18A1-11d1-B105-00805F49916B
--------------------------------------------------------
, Billy Johnson, Samantha Johnson

Finally, if we wrap our FOR XML PATH select statement in the STUFF function, we can obtain our desired output with the following query:

SELECT p.ParentId
            , p.FirstName
            , p.LastName
            , p.Age
            , STUFF(
                  (SELECT ', ' + c.FullName
                        FROM @Children c
                        WHERE c.ParentId=p.ParentId
                        FOR XML PATH('')
                  ), 1, 2, '') AS Children
FROM @Parents p

Once again, we see that almost anything is possible with enough SELECT statements.