Database Programming: The String Concatenation XML Trick, Finalized

It's an especially Good Friday when we can close the loop on a technical conversation, and I believe that our modifications to The Technique That Lance Found, also discussed here and here, are complete. Scott Whigham left a comment on the most recent post offering an XML implementation that will run in AdventureWorks.  A little fiddling with Scott's syntax yields this approach to the original issue:

;WITH ColumnToPivot ([data()]) AS (
    SELECT p.ParentString + N', '
    FROM Parent p
    JOIN Child c
    ON c.ParentId = p.ParentId
    WHERE c.ChildId = 2
    ORDER BY p.ParentId
    FOR XML PATH(''), TYPE
),
    XmlRawData (CSVString) AS (
        SELECT (SELECT [data()] AS mydata FROM ColumnToPivot AS d FOR XML RAW, TYPE).value( '/row[1]/mydata[1]', 'NVARCHAR(max)') AS CSV_Column
)
SELECT LEFT(CSVString, LEN(CSVString)-1) AS CSVList
FROM XmlRawData

This construction has a number of things going for it. It seems to be the fastest of the lot (no nested SELECTs!), which is always nice, and it manages to impose an ORDER BY without resorting to the use of TOP, so we're out of the "dirty trick" business. It also strikes me as the easiest of the bunch to read.

For the record, here's the result set from this latest syntax:

CSV_List

<Parent 1 String>,<Parent 2 String>,<Parent 3 String>

Thanks again to Lance Larsen, Adam Machanic, and Scott Whigham for contributing to this conversation. Isn't community grand?

     -wp

Comments

• 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
  I’ve got to pay more punctual attention to my comment pool.. RBarryYoung’s movingsql.com will be on my

• 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 24, 2008
  Very cool - I love the community approach!