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, Finalized

Database Programming: The String Concatenation XML Trick, Finalized

  • Comments 5
  • Likes

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

  • 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

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