다음을 통해 공유

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:

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

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

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

  • Anonymous
    January 01, 2003
    A find shared by one friend leads to correspondence from another.. The redoubtable Adam Machanic left

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

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

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

  • Anonymous
    March 14, 2008
    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

  • Anonymous
    May 08, 2009
    Thanks very much. That did solve my problem.