Rows to Comma Separated Lists in SQL Server

A useful bit of syntax in the case that you end up with multiple levels of granularity in a dataset, and you want to collapse it back down. One solution would be to turn your rows into columns (pivoting), or put your row values into a single column separated by commas (or pipes, etc.). Below I’m using the AdventureWorks2017 database to show how to do the latter.

SELECT [CountryRegionCode]
      ,StateProvinceCode
FROM [AdventureWorks2017].[Person].[StateProvince]

Here it produces a gigantic list of countries with associated state/provinces. So let’s collapse that down…

SELECT outerQ.[CountryRegionCode],
STUFF((
		SELECT DISTINCT ',' + subQ.StateProvinceCode 
		FROM [AdventureWorks2017].[Person].[StateProvince] subQ 
		WHERE outerQ.CountryRegionCode = subQ.CountryRegionCode 
		FOR XML PATH('')
	), 1, 1, ''
) as LastNameList
FROM [AdventureWorks2017].[Person].[StateProvince] outerQ
GROUP BY outerQ.[CountryRegionCode];