Pivoting Rows into Columns

Applicability:

SQL Server 2000: N/A

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Tested

SQL Server 2014: Tested

SQL Server 2016: Tested

SQL Server 2017: Tested

Credits:

Author: ChillyDBA

Date: 10 Jun 2020

Description

This is not a script for direct use, but more of a template for use in other scripts.

This allows multiple rows from a single column to be pivoted with a delimiter into a single column on a single row. The resulting delimited string has no leading delimiter value.

The query can be a single table subquery, a correlated subquery, or a multi-join subquery of either type

The code here is wrapped in an example that will be easy to customize/test and which illustrates the output effectively.

Code

-- Conditionally drop the test table to make reruns easier. IF OBJECT_ID('tempdb..#TestData','U') IS NOT NULL DROP TABLE #TestData;CREATE TABLE #TestData (AccountNumber INT,Value CHAR(3));-- Build 1000 account numbers with random 3 character data. ;WITHTENS (N) AS (SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALLSELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALLSELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0),THOUSANDS (N) AS (SELECT 1 FROM TENS t1 CROSS JOIN TENS t2 CROSS JOIN TENS t3),MILLIONS (N) AS (SELECT 1 FROM THOUSANDS t1 CROSS JOIN THOUSANDS t2),TALLY (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM MILLIONS)INSERT INTO #TestDataSELECT TOP (100000)AccountNumber = CASE WHEN (N%1000) = 0 THEN 1000 ELSE N%1000 END,Value = CHAR(CONVERT(INT,RAND(CHECKSUM(NEWID()))*10)+64) +CHAR(CONVERT(INT,RAND(CHECKSUM(NEWID()))*10)+64) +CHAR(CONVERT(INT,RAND(CHECKSUM(NEWID()))*10)+64)FROM TALLY;-- Add a clustered index to the table CREATE CLUSTERED INDEX IX_#TestData_Cover ON #TestData (AccountNumber, Value);select * from #TestData;WITH CTE AS(SELECT DISTINCTAccountNumberFROM #TestData)SELECT AccountNumber,CommaList = STUFF((SELECT ',' + ValueFROM #TestDataWHERE AccountNumber = CTE.AccountNumberORDER BY ValueFOR XML PATH(''),TYPE).value('.','varchar(max)'),1,1,'')FROM CTEORDER BY AccountNumber;drop table #testdata