MSSQL 2012: flattening a parent-child relationship using an XML 'CSV' trick

Ever been faced with a scenario where you know that a particular data relationship will 'always' have a limited number of children, and your UI requirements require tight coupling of parent data with the child collection?  

Sounds like we're about to travel thru a longish diatribe of data normalization do's and don'ts.    Dont worry, bee happy, we's not!

I'm writing this simply to document something that employs a fairly obscure but quite useful T-SQL "XML" technique.. that does just this, putting some powerful 'data-shaping logic' in your hands.

Example:   you have a parent table called 'parent' (and a child collection, called, of all things, 'child').  

The parent table has this structure:

id
parentName
address

The child table has this structure:

id
parentId
childName


Your current app requirements, for example, describe a read-only display, where there is no need for 2 related 'models' to be carried down from server to client.

You want to return, in one stored proc call, a result set that looks like this:

id, parentName, address, "childname1, childname2, childnameXX"

To make life super-easy for UI rendering, we want to return 4 columns.. the 4th being a concatenated 'CSV style' list of related children-properties.. in this case, their names).

Here's the Parent Table

the Child Table

the Stored Procedure ResultSet

And finally, working code follows for the T-SQL Stored Procedure:

Hope you find this as useful as I have.   Enjoy!      -bob



CREATE PROCEDURE  getParentsWithChildrenNames
AS

-- first, we declare a table-var to shape the final result set:

declare @tbl TABLE 
 (
id int,
parentName varchar(100),
address varchar(100),
        childNames varchar(max)
 )

 declare @tblChild TABLE 
  (
parentId int,
childName varchar(100)
  )

INSERT INTO @tbl (id,parentName ,address ,childNames) 
SELECT id, parentName, address, ''
FROM Parent

INSERT INTO @tblChild (parentId, childName ) 
 SELECT  parentId, childName
  FROM child C   
  INNER JOIN parent P on C.id = P.id

-- use 'group-by concatenation' to flatten: (this trick relies upon a well-known XML PATH undoc'd feature, which allows children to be efficiently concatenated) 
UPDATE @tbl
SET childNames = C.childrenString
FROM (SELECT p1.parentId,
  ( SELECT (rtrim(childName) + ',')  
  FROM @tblChild p2
 WHERE p2.parentId = p1.parentId
 ORDER BY childName
FOR XML PATH('') ) AS childrenString
 FROM @tblChild p1
 GROUP BY parentId) C

SELECT * FROM @tbl ORDER BY parentName


-- getParentsWithChildrenNames