Cool Tips‎ > ‎Scripting SQL Server‎ > ‎

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. ;WITH TENS (N) AS (SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 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 #TestData SELECT 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 DISTINCT AccountNumber FROM #TestData ) SELECT AccountNumber, CommaList = STUFF(( SELECT ',' + Value FROM #TestData WHERE AccountNumber = CTE.AccountNumber ORDER BY Value FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,1,'') FROM CTE ORDER BY AccountNumber; drop table #testdata





Comments