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
