Database Programming: The String Concatenation XML Trick, Sans Entitization

re: Database Programming: The String Concatenation XML Trick, Sans Entitization

Scott Whigham — Wed, 19 Mar 2008 17:50:56 GMT

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

FROM Person.Contact

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 (

SELECT *

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[1]/mydata[1]', 'NVARCHAR(max)') AS CSV_Column

)

SELECT LEFT(CSVString, LEN(CSVString)-1) AS CSVList

FROM XmlRawData

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 :)

Scott Whigham

http://www.learnsqlserver.com/

Database Programming: The String Concatenation XML Trick, Finalized

Ward Pond's SQL Server blog — Fri, 21 Mar 2008 17:15:29 GMT

It's an especially Good Friday when we can close the loop on a technical conversation, and I believe

http://sqlservercode.blogspot.com/2008/03/links-of-week-20080322.html

TrackBack — Mon, 24 Mar 2008 00:28:39 GMT

May Glad Tidings be Upon You

Ward Pond's SQL Server blog — Sat, 20 Dec 2008 18:55:09 GMT

UPDATED 20 Dec 2008 to fix links It’s that time of year again, when I disappear from the blogosphere

Database Programming: The String Concatenation XML Trick, Revisited

Ward Pond's SQL Server blog — Fri, 27 Feb 2009 06:31:16 GMT

I’ve got to pay more punctual attention to my comment pool.. RBarryYoung’s movingsql.com will be on my

T-SQL Challenge: Grouped String Concatenation

Adam Machanic — Fri, 27 Feb 2009 21:25:05 GMT

It's been quite a while since the LIKE vs ? Puzzle , and I feel like it's time for another one. Response