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 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:
Thanks again to Lance Larsen, Adam Machanic, and Scott Whigham for contributing to this conversation. Isn't community grand?
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