Show child rows as CSV values in SQL Server data relationships

Published


It's a pretty simple requirement, but given two tables (parent and child data), we just need to show the child data as a list alongside each parent row.  The problem with the million examples online is that they either use only one table to begin with, or are too complicated to learn from and adapt!

The Parent/Child Data

We are using two tables, parent and child described simply as follows:

DECLARE @tbl_parent TABLE (ID int IDENTITY (1, 1),    ParentTitle nvarchar(max));
DECLARE @tbl_child TABLE (ID int IDENTITY (1, 1), ParentId int,    ChildTitle nvarchar(max));

INSERT INTO @tbl_parent    (ParentTitle) VALUES ('Parent Row 1'), ('Parent Row 2');
INSERT INTO @tbl_child (ParentID, ChildTitle) VALUES (1, 'Child Row 1'), (1, 'Child Row 2'), (2, 'Child Row 3');

SELECT * FROM @tbl_parent;
SELECT * FROM @tbl_child;

SELECT
    p.ID,
    p.ParentTitle,
    c.ChildTitle
FROM
    @tbl_parent p INNER JOIN
    @tbl_child c ON p.ID = c.ParentId;

The result of this basic code is as follows:

TSQL Parent and Child data

The Goal

This is what we want to achieve, each parent show listed, plus all of the child rows shown as CSV lists.  If you want the solution with an explanation, just skip to the end...

TSQL Parent data with child rows shown as CSV lists 

Placing Child Rows alongside their Parent Row

The first step is to get rid of the parent rows being shown for each child row, so that we only see the parent row once:

;WITH cte AS
    ( /* The common table expression holds our original parent-child query showing ALL parent/child rows */
    SELECT
        p.ID AS [ParentID],
        p.ParentTitle,
        c.ChildTitle
    FROM
        @tbl_parent p INNER JOIN
        @tbl_child c ON p.ID = c.ParentId
    )
SELECT
    *
FROM
    @tbl_parent p CROSS APPLY
    (
    SELECT
        ChildTitle
    FROM
        cte
    WHERE
        cte.ParentID = p.ID
    FOR XML PATH ('')) pc (someCol);

The CTE holds our original query (thus we are actually creating a single table as per the million other examples online, which we then work with).  The FOR XML line forces the list of child rows into a single XML string for the parent, and CROSS APPLY makes this happen on every parent row.

TSQL Parent data with child rows shown as XML strings

Replace the XML with Comma Seperation

Now we have our XML children, we simply add the comma to the end of our child column, which makes SQL automatically replace the XML:

;WITH cte AS
    ( /* The common table expression holds our original parent-child query showing ALL parent/child rows */
    SELECT
        p.ID AS [ParentID],
        p.ParentTitle,
        c.ChildTitle
    FROM
        @tbl_parent p INNER JOIN
        @tbl_child c ON p.ID = c.ParentId
    )
SELECT
    p.ID,
    p.ParentTitle,
    pc.childRowList
FROM
    @tbl_parent p CROSS APPLY
    (
    SELECT
        ChildTitle + ','  /* <--- Note the comma here, this automatically makes SQL remove the XML */
    FROM
        cte
    WHERE
        cte.ParentID = p.ID
    FOR xml PATH ('')) pc (childRowList);

Now we have this, which is good enough for most people:

TSQL Parent data with child rows shown as CSV lists

Remove the trailing commas

Heading for perfection, the final step is to remove the trailing comma.  This is done by putting it at the start, then using the STUFF function to replace the first character (the now leading comma) with a blank, thus deleting it:

;WITH cte AS
    ( /* The common table expression holds our original parent-child query showing ALL parent/child rows */
    SELECT
        p.ID AS [ParentID],
        p.ParentTitle,
        c.ChildTitle
    FROM
        @tbl_parent p INNER JOIN
        @tbl_child c ON p.ID = c.ParentId
    )
SELECT
    p.ID,
    p.ParentTitle,
    STUFF(pc.childRowList, 1, 1, '') /* Remove the first comma */
FROM
    @tbl_parent p CROSS APPLY
    (
    SELECT
        ', ' + ChildTitle /* Put the comma at the start */
    FROM
        cte
    WHERE
        cte.ParentID = p.ID
    FOR xml PATH ('')) pc (childRowList); 

 TSQL Parent data with child rows shown as CSV lists

Comments

No comments have been posted. Be the first to write a comment...

New Comment





This is to prove that you are not a spam robot posting links to dodgy sites!