Concatenate/Pivot Data with FOR XML–and Count It

I recently needed to concatenate or pivot some data into a comma-delimited list. There are numerous examples around (Ken Simmons, Rob Farley) showing how to accomplish this by using FOR XML PATH and the STUFF function. My only addition is to add the ability to count the number of items in each list. Here is a simple example to get started.

-- Uses syntax for SQL Server 2008 R2
DECLARE @table TABLE (value varchar(10));

INSERT INTO @table VALUES ('A'),('B'),('C'),('D');

-- Pivot with STUFF and FOR XML PATH
SELECT STUFF((SELECT ',' + value FROM @table
FOR XML PATH('')), 1, 1, '') as val;

Here is the result:

Often it isn’t so useful to just rotate a single column to get a list. Instead, you may want to rotate a list of values per some group of divisions. You might want a list per state or per department. Here is an example that shows how to rotate per a division.

-- Take it a step further with additional values
DECLARE @table2 TABLE (division int, value varchar(10));

INSERT INTO @table2 VALUES (1,'A'),(2, 'B'),
                   (1, 'C'),(2,'D'),(3,'E');

-- Pivot with STUFF and FOR XML PATH and DISTINCT
SELECT DISTINCT division,
  STUFF((SELECT ',' + value FROM @table2 t2 WHERE t1.division = t2.division
  FOR XML PATH('')), 1, 1, '') as val
FROM @table2 t1;

Here is the result:

For my purpose it was also going to be handy to know the number of items in each list. With just a couple of tweaks you can get the count of values per division at the same time as the rotate and even drop the DISTINCT.

-- Take it one more step doing groupings and counts</span>
DECLARE @table3 TABLE (division int, value varchar(10));

INSERT INTO @table3 VALUES (1,'A'),(2, 'B'),(1, 'C'),(2,'D'),(3,'E');

-- Pivot with STUFF and FOR XML PATH and DISTINCT
SELECT division, COUNT(*) as divisioncount,
  STUFF((SELECT ',' + value
  FROM @table3 t2 WHERE t1.division = t2.division
  FOR XML PATH('')), 1, 1, '') as val
FROM @table3 t1
GROUP BY division;

Here is the final result:

Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *