When last we checked in on The Technique That Lance Found, Adam had noted that the method entitizes XML special characters, a state of affairs which limits its utility somewhat. I tried to leverage Tony Rogerson's technique, which Adam passed along in his comment, but it was late and I was tired, so that didn't go so well. I provided a relatively lame proof-of-concept using nested REPLACEs and called it an evening.
Well, it's amazing what a couple of good nights' sleep will do (not to mention clearing several important deliverables from my plate). I present herewith the Rogerson Permutation of The Technique That Lance Found:
-- PERFORM THE TRICK WITH THE ROGERSON MODIFICATION -- PIVOT Parent VALUES INTO 1 COLUMN FOR 1 BASE ROW
SELECT LEFT(Parent_CSV,LEN(Parent_CSV)-1) FROM ( SELECT (SELECT mydata FROM ( SELECT x AS [data()] FROM ( SELECT ParentString + N', ' FROM ( SELECT TOP 100 PERCENT Child.ParentId, Parent.ParentString FROM Child JOIN Parent ON Child.ParentId = Parent.ParentId WHERE Child.ChildId = 2 -- MUST SPECIFY 1 BASE ROW. COULD BE A CORRELATED SUBQUERY ORDER BY Child.ParentId ) a ) AS y (x) FOR XML PATH(''), TYPE ) AS d ( mydata ) FOR XML RAW, TYPE ).value( '/row/mydata', 'varchar(max)' ) AS Parent_CSV ) a
The results here address Adam's concern:
I'm not wild about the TOP 100 PERCENT/ORDER BY combination, as that's something of a dirty trick. When I have a little more time I'll look at using a CTE to get the ordered result.
Thanks to Adam for the link to Tony's work, and to Tony for having it there in the first place.
More to come..
I love this :) Fun stuff! Here's a CTE version using AdventureWorks - 11ms was my execution time for the CTE - superfast -
-- List of all Production.ProductSubcategories that a contact has ordered:
-- Choose contact that has ordered the most items
DECLARE @ContactId INT
SELECT @ContactId = ContactId
WHERE ContactId = (SELECT TOP(1) ContactId FROM Sales.SalesOrderHeader GROUP BY ContactId ORDER BY COUNT(*) DESC);
WITH ColumnToPivot ([data()]) AS (
SELECT TOP 100 PERCENT ps.Name + N', ' AS ColumnYouWantToMakeACSVList_PlusAComma
FROM Production.ProductSubcategory ps
WHERE EXISTS (
FROM Production.Product p JOIN Sales.SalesOrderDetail od
ON od.ProductId = p.ProductId
JOIN Sales.SalesOrderHeader o
ON o.SalesOrderId = od.SalesOrderId
WHERE o.ContactId = @ContactId
AND p.ProductSubcategoryID = ps.ProductSubcategoryID
ORDER BY ColumnYouWantToMakeACSVList_PlusAComma -- better to just order by ps.Name but, for example purposes, I include this
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
I hope the formatting looks okay... I moved the FOR XML clause from a nested derived table to the main CTE - I couldn't find fault with it but perhaps I missed something?
Thanks for sharing, guys :)
It's an especially Good Friday when we can close the loop on a technical conversation, and I believe
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