Database Programming: The String Concatenation XML Trick

Courtesy of my good friend and once-and-always colleague, Lance Larsen, who writes:

I recently ran into this little trick. Joining two tables having a one-to-many relationship and stuffing a set of column values from the many side into a single column on the one side. Works only for one base row at a time – I found this as a correlated subquery in a much larger query. Might be able to do this as a function too but I thought it was interesting.

Here's a distillation of the code Lance ran into:

--LAY THE GROUNDWORK
--DROP TABLE Parent

create table Parent
(ParentID INT
,ParentString VARCHAR(100)

)

INSERT Parent VALUES (1, 'Parent 1 String')

INSERT Parent VALUES (2, 'Parent 2 String')

INSERT Parent VALUES (3, 'Parent 3 String')

-- DROP TABLE Child

create table Child

(ChildId INT

,ParentID INT

)

INSERT Child VALUES (1, 1)
INSERT Child VALUES (2, 1)

INSERT Child VALUES (2, 2)

INSERT Child VALUES (2, 3)

INSERT Child VALUES (3, 1)

INSERT Child VALUES (3, 3)

-- SHOW THE DATA

SELECT  Child.ChildId, ISNULL(Parent.ParentString, '')

FROM    Child

INNER   JOIN Parent

ON      Child.ParentID = Parent.ParentID

ORDER BY Child.ChildId, Child.ParentID

-- PERFORM THE TRICK

-- PIVOT Parent VALUES INTO 1 COLUMN FOR 1 BASE ROW

SELECT  STUFF(( SELECT [text()]= ',' + ISNULL(Parent.ParentString, '') + ''

FROM    Child

JOIN    Parent

ON      Child.ParentID = Parent.ParentID

WHERE   Child.ChildId = 2 -- MUST SPECIFY 1 BASE ROW. COULD BE A CORRELATED SUBQUERY

ORDER BY Child.ParentID

FOR XML PATH('')), 1,1, '') AS Parent_CSV

This code produces two sets of output. The first shows the relationships between parents and children:

.. and the second shows the concatenated result for a single key value (2, in this case):

I find this to be an incredibly slick approach, both in its devious use of XML and its ingenious use of the STUFF function to remove the leading comma from the first concatenated value.

Thanks, Lance, for passing this along!

     -wp