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 =— 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:
























ChildId (No column name)
1 Parent 1 String
2 Parent 1 String
2 Parent 2 String
2 Parent 3 String
3 Parent 1 String
3 Parent 3 String

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







Parent_CSV
Parent String 1, Parent String 2, Parent String 3

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

Comments (8)

  1. Anonymous says:

    I’ve got to pay more punctual attention to my comment pool.. RBarryYoung’s movingsql.com will be on my

  2. Anonymous says:

    When last we checked in on The Technique That Lance Found , Adam had noted that the method entitizes

  3. Anonymous says:

    A find shared by one friend leads to correspondence from another.. The redoubtable Adam Machanic left

  4. Anonymous says:

    UPDATED 20 Dec 2008 to fix links It’s that time of year again, when I disappear from the blogosphere

  5. Anonymous says:

    It’s an especially Good Friday when we can close the loop on a technical conversation, and I believe

  6. Anonymous says:

    It’s been quite a while since the LIKE vs ? Puzzle , and I feel like it’s time for another one. Response

  7. Adam Machanic says:

    This is a great technique, but there is a bit of a gotcha if you have any "special" characters in your strings (they can be "entitized" due to the fact that the FOR XML option is designed to produce XML) — more info here:

    http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/07/06/871.aspx

  8. z says:

    Thanks very much. That did solve my problem.