XML Path Use Case: Pivot Row Data

Yesterday, one of my co-worker wanted to learn combining rows into a single comma delimited string, so she can use the result in an IN operator part of a WHERE clause. In this post, I'll provide an example where XML PATH mode can be used to convert a rowset to a single string. The query below is very handy when you work with pivoting data or need a string to use with the IN clause.

USE AdventureWorks2012;
GO
-- all addresses
SELECT * 
FROM Person.Address;

-- Number of addresses in each city in Washington state.
SELECT sp.Name AS StateName, a.City, COUNT(a.AddressID) AS cnt
FROM Person.Address AS a
JOIN [Person].[StateProvince] AS sp ON a.StateProvinceID = sp.StateProvinceID
WHERE sp.StateProvinceCode = 'WA'
GROUP BY sp.Name, a.City
ORDER BY a.City;

--
-- How about list top 10 cities in Washington state with the most addresses
SELECT TOP(10) sp.Name AS StateName, a.City, COUNT(a.AddressID) AS cnt
FROM Person.Address AS a
JOIN [Person].[StateProvince] AS sp ON a.StateProvinceID = sp.StateProvinceID
WHERE sp.StateProvinceCode = 'WA'
GROUP BY sp.Name, a.City
ORDER BY cnt DESC;

--
--Let's pivot top 10 cities in Washington state with the most addresses
SELECT piv.* 
FROM (
 SELECT TOP(10) sp.Name AS StateName, a.City, COUNT(a.AddressID) AS cnt
 FROM Person.Address AS a
 JOIN [Person].[StateProvince] AS sp ON a.StateProvinceID = sp.StateProvinceID
 WHERE sp.StateProvinceCode = 'WA'
 GROUP BY sp.Name, a.City
 ORDER BY cnt DESC
) AS src
PIVOT (
  MAX(cnt) FOR City IN ("Burien","Bellingham","Bremerton","Seattle","Redmond","Everett","Issaquah","Edmonds","Renton","Lynnwood")
) AS piv;

--
-- What if you don't want to manually type all the 10 cities in the IN operator above? 
-- Here's how to combine all 10 cities into one single string then copy and paste.
SELECT '"' + src.City + '",'
FROM (
 SELECT TOP(10) a.City, COUNT(a.AddressID) AS cnt
 FROM Person.Address AS a
 JOIN [Person].[StateProvince] AS sp ON a.StateProvinceID = sp.StateProvinceID
 WHERE sp.StateProvinceCode = 'WA'
 GROUP BY a.City
 ORDER BY cnt DESC
) AS src
FOR XML PATH('');

--
-- Last but not least, here's how to delimit the cities by comma 
--    and enclose each city with a pair of single quote.
SELECT DISTINCT '''' +  a.city  + ''',' 
FROM Person.Address AS a
JOIN [Person].[StateProvince] AS sp ON a.StateProvinceID = sp.StateProvinceID
WHERE sp.StateProvinceCode = 'WA'
FOR XML PATH('');

That's all for now. Finally we can apply what we learned on last month XML posts.