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.