Ward Pond's SQL Server blog

Ruminating on issues pertinent to the design and development of sound databases and processes under Microsoft SQL Server 2008, SQL Server 2005, and SQL Server 2000 (while reserving the right to vent about anything else that's on my mind)

Database Programming: The String Concatenation XML Trick

Database Programming: The String Concatenation XML Trick

  • Comments 8
  • Likes

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
  • 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

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

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

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

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

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

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

  • Thanks very much. That did solve my problem.

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment