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/mydata’, ‘NVARCHAR(max)’) AS CSV_Column
SELECT LEFT(CSVString, LEN(CSVString)-1) AS CSVList
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:
|<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?