In this blog post I want to discuss a problem I encounter frequently in SQL related forums – how to perform dynamic PIVOT involving multiple columns using pure T-SQL solution. In SQL Server Reporting Services this functionality can be easily achieved using Matrix template.

With the introduction of PIVOT operator in SQL 2005, it became very easy to write queries that transform rows into columns. There are numerous articles on the Web as how to perform PIVOT and even dynamic PIVOT. However, most of these articles explain how to perform such queries for just one column. I want to expand the transformation for multiple columns.

The idea of a solution is quite simple – you need to generate the SQL dynamically. Since we want to use pivot on multiple columns, we need to use CASE based pivot.

You need to keep in mind, that resulting query should have less than 1024 columns. You may build the check for number of columns into the query.

Now, every time I need to create a dynamic query, I need to know what is the final query I am going to arrive to. Having the idea in mind and printing the SQL command until I got it right helps to create the working query.

Let’s consider the first example based on the AdventureWorks SalesOrderHeader table.
This code creates the table we’re going to transform – it gives us summary of orders and total due per each quarter:

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
USE AdventureWorks 
 
SELECT   DATEPART(quarter,OrderDate) AS [Quarter], 
         DATEPART(YEAR,OrderDate)    AS [Year], 
         COUNT(SalesOrderID)         AS [Orders Count], 
         SUM(TotalDue)               AS [Total Due] 
INTO     #SalesSummary 
FROM     Sales.SalesOrderHeader 
GROUP BY DATEPART(quarter,OrderDate), 
         DATEPART(YEAR,OrderDate) 
 
SELECT   * 
FROM     #SalesSummary 
ORDER BY [Year], 
         [Quarter]
USE AdventureWorks 

SELECT   DATEPART(quarter,OrderDate) AS [Quarter], 
         DATEPART(YEAR,OrderDate)    AS [Year], 
         COUNT(SalesOrderID)         AS [Orders Count], 
         SUM(TotalDue)               AS [Total Due] 
INTO     #SalesSummary 
FROM     Sales.SalesOrderHeader 
GROUP BY DATEPART(quarter,OrderDate), 
         DATEPART(YEAR,OrderDate) 

SELECT   * 
FROM     #SalesSummary 
ORDER BY [Year], 
         [Quarter]

The #SalesSummary table lists count of orders and total due for each quarter and year.

Quarterly Orders Summary
Quarter Year Orders Count Total Due
3 2001 621 $5,850,932.9483
4 2001 758 $8,476,619.278
1 2002 741 $7,379,686.3091
2 2002 825 $8,210,285.1655
3 2002 1054 $13,458,206.13
4 2002 1072 $10,827,327.4904
1 2003 1091 $8,550,831.8702
2 2003 1260 $10,749,269.374
3 2003 4152 $18,220,131.5285
4 2003 5940 $16,787,382.3141
1 2004 6087 $14,170,982.5455
2 2004 6888 $17,969,750.9487
3 2004 976 $56,178.9223

Now, suppose we want to see these results horizontally. The following dynamic SQL produces the desired output:

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
DECLARE  @SQL  NVARCHAR(MAX), 
         @Cols NVARCHAR(MAX)
         
SELECT @Cols = STUFF((select ', 
SUM(CASE WHEN [Quarter]=' + CAST([Quarter] as varchar(3)) + ' AND [Year] = ' + 
CAST([Year] as char(4)) + 
' THEN [Orders Count] ELSE 0 END) AS [' + 
CAST([Year] as char(4)) + '-' + CAST([Quarter] as varchar(3)) + 
' Orders], 
SUM(CASE WHEN [Quarter]=' + CAST([Quarter] as varchar(3)) + 
' AND [Year] = ' + CAST([Year] as char(4)) + 
' THEN [Total Due] ELSE 0 END) AS [' + CAST([Year] as char(4)) + '-' + 
CAST([Quarter] as varchar(3)) + ' Sales]'
FROM #SalesSummary 
ORDER BY [Year],[Quarter] FOR XML PATH(''),type).value('.','varchar(max)'),1,2,'')
 
SET @SQL = 'SELECT ' + @Cols + '  FROM #SalesSummary' 
 
--print @SQL 
EXECUTE( @SQL)
DECLARE  @SQL  NVARCHAR(MAX), 
         @Cols NVARCHAR(MAX)
         
SELECT @Cols = STUFF((select ', 
SUM(CASE WHEN [Quarter]=' + CAST([Quarter] as varchar(3)) + ' AND [Year] = ' + 
CAST([Year] as char(4)) + 
' THEN [Orders Count] ELSE 0 END) AS [' + 
CAST([Year] as char(4)) + '-' + CAST([Quarter] as varchar(3)) + 
' Orders], 
SUM(CASE WHEN [Quarter]=' + CAST([Quarter] as varchar(3)) + 
' AND [Year] = ' + CAST([Year] as char(4)) + 
' THEN [Total Due] ELSE 0 END) AS [' + CAST([Year] as char(4)) + '-' + 
CAST([Quarter] as varchar(3)) + ' Sales]'
FROM #SalesSummary 
ORDER BY [Year],[Quarter] FOR XML PATH(''),type).value('.','varchar(max)'),1,2,'')

SET @SQL = 'SELECT ' + @Cols + '  FROM #SalesSummary' 

--print @SQL 
EXECUTE( @SQL)

It produces the transformed result (I show only few first columns):

Transformed result
2001-3 Orders 2001-3 Sales 2001-4 Orders 2001-4 Sales 2002-1 Orders 2002-1 Sales 2002-2 Orders 2002-2 Sales
621 $5,850,932.9483 758 $8,476,619.278 741 $7,379,686.3091 825 $8,210,285.1655

You may want to un-comment PRINT @SQL statement if you want to see the generated command. I used XML PATH to concatenate rows into a string based on this blog post by Brad Schulz Making a list and checking it twice.

Another interesting problem was presented in this MSDN Transact-SQL forum thread:

For the claims table that has many integer columns, transform rows into columns. I demonstrate just the solution with the table creation script:

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
USE tempdb 
 
IF OBJECT_ID('Claims','U') IS NOT NULL 
  DROP TABLE Claims 
 
GO 
 
CREATE TABLE Claims ( 
  Claim  INT, 
  HCPC   INT, 
  [mod]  INT, 
  charge INT, 
  paid   INT, 
  Qty    INT) 
 
INSERT INTO Claims 
SELECT 12345, 
       99245, 
       90, 
       20, 
       10, 
       1 
UNION ALL 
SELECT 12345, 
       99112, 
       NULL, 
       30, 
       20, 
       1 
UNION ALL 
SELECT 12345, 
       99111, 
       80, 
       50, 
       25, 
       2 
UNION ALL 
SELECT 11112, 
       99911, 
       60, 
       50, 
       20, 
       1 
UNION ALL 
SELECT 12222, 
       99454, 
       NULL, 
       50, 
       20, 
       1 
 
SELECT * FROM Claims
USE tempdb 

IF OBJECT_ID('Claims','U') IS NOT NULL 
  DROP TABLE Claims 

GO 

CREATE TABLE Claims ( 
  Claim  INT, 
  HCPC   INT, 
  [mod]  INT, 
  charge INT, 
  paid   INT, 
  Qty    INT) 

INSERT INTO Claims 
SELECT 12345, 
       99245, 
       90, 
       20, 
       10, 
       1 
UNION ALL 
SELECT 12345, 
       99112, 
       NULL, 
       30, 
       20, 
       1 
UNION ALL 
SELECT 12345, 
       99111, 
       80, 
       50, 
       25, 
       2 
UNION ALL 
SELECT 11112, 
       99911, 
       60, 
       50, 
       20, 
       1 
UNION ALL 
SELECT 12222, 
       99454, 
       NULL, 
       50, 
       20, 
       1 

SELECT * FROM Claims

The output is:

Claims table
Claim HCPC mod Charge Paid Qty
12345 99245 90 20 10 1
12345 99112 NULL 30 20 1
12345 99111 80 50 25 2
11112 99911 60 50 20 1
12222 99454 NULL 50 20 1

And the following code transforms the result (as you see, I am using a loop and ROW_NUMBER() approach):

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
DECLARE  @SQL     NVARCHAR(MAX), 
         @Loop    INT, 
         @MaxRows INT 
 
SET @Sql = '' 
 
SELECT @MaxRows = MAX(MaxRow) 
FROM   (SELECT   COUNT(* ) AS MaxRow, 
                 Claim 
        FROM     Claims 
        GROUP BY Claim) X 
 
SET @Loop = 1 
 
WHILE @Loop <= @MaxRows 
  BEGIN 
    SELECT @SQL = @SQL + ',     SUM(CASE WHEN Row = ' + CAST(@Loop AS VARCHAR(10)) + ' THEN ' + QUOTENAME(Column_Name) + ' END) AS [' + COLUMN_NAME + CAST(@Loop AS VARCHAR(10)) + ']'
    FROM   INFORMATION_SCHEMA.COLUMNS 
    WHERE  TABLE_Name = 'Claims' 
           AND COLUMN_NAME NOT IN ('Row','Claim') 
     
    SET @Loop = @Loop + 1 
  END 
 
--PRINT @SQL 
SET @SQL = 'SELECT Claim' + @SQL + ' FROM (select *,          row_number() over (partition by Claim ORDER BY Claim) as Row         FROM Claims) X GROUP BY Claim ' 
 
PRINT @SQL 
 
EXECUTE( @SQL)
DECLARE  @SQL     NVARCHAR(MAX), 
         @Loop    INT, 
         @MaxRows INT 

SET @Sql = '' 

SELECT @MaxRows = MAX(MaxRow) 
FROM   (SELECT   COUNT(* ) AS MaxRow, 
                 Claim 
        FROM     Claims 
        GROUP BY Claim) X 

SET @Loop = 1 

WHILE @Loop <= @MaxRows 
  BEGIN 
    SELECT @SQL = @SQL + ',     SUM(CASE WHEN Row = ' + CAST(@Loop AS VARCHAR(10)) + ' THEN ' + QUOTENAME(Column_Name) + ' END) AS [' + COLUMN_NAME + CAST(@Loop AS VARCHAR(10)) + ']'
    FROM   INFORMATION_SCHEMA.COLUMNS 
    WHERE  TABLE_Name = 'Claims' 
           AND COLUMN_NAME NOT IN ('Row','Claim') 
     
    SET @Loop = @Loop + 1 
  END 

--PRINT @SQL 
SET @SQL = 'SELECT Claim' + @SQL + ' FROM (select *,          row_number() over (partition by Claim ORDER BY Claim) as Row         FROM Claims) X GROUP BY Claim ' 

PRINT @SQL 

EXECUTE( @SQL)
Transformed Output
Claim HCPC1 mod1 Charge1 Paid1 Qty1 HCPC2 mod2 Charge2 Paid2 Qty2 HCPC3 mod3 Charge3 Paid3 Qty3
11112 99911 60 50 20 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
12222 99454 NULL 50 20 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
12345 99245 90 20 10 1 99112 NULL 30 20 1 99111 80 50 25 2

I’ll post another recent problem found in the following MSDN thread Basic Crosstab Query

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
SET NOCOUNT ON;
GO
USE tempdb;
GO
CREATE TABLE #T (
StoreID char(5) NOT NULL,
CriteriaNo int NOT NULL,
Result int NOT NULL, 
Position int NOT NULL,
PRIMARY KEY (StoreID, CriteriaNo)
);
GO
INSERT INTO #T(StoreID, CriteriaNo, Result, Position)
SELECT '0001', 9, 10, 1 UNION ALL 
SELECT '0002', 9, 12, 2 UNION ALL 
SELECT '0001', 10, 5, 1 UNION ALL
SELECT '0002', 10, 6, 2;
GO
-- dynamic
DECLARE @sql nvarchar(MAX), @Cols nvarchar(max);
 
SELECT @Cols = (select ', ' + 'MAX(case when CriteriaNo = ' + CONVERT(varchar(20), CriteriaNo) + 
' then Result else 0 end) AS CriteriaNo' + CONVERT(varchar(20), CriteriaNo) + 
', MAX(case when CriteriaNo = ' + CONVERT(varchar(20), CriteriaNo) + 
' then Position else 0 end) AS CriteriaPosition' + CONVERT(varchar(20), CriteriaNo)
from (select distinct CriteriaNo from #T) X ORDER By CriteriaNo 
FOR XML PATH(''))
 
SET @sql = 'SELECT StoreID' + @Cols + ', SUM(Result) as PerformancePrint
FROM #T
GROUP BY StoreID'
 
EXECUTE(@sql)
SET NOCOUNT ON;
GO
USE tempdb;
GO
CREATE TABLE #T (
StoreID char(5) NOT NULL,
CriteriaNo int NOT NULL,
Result int NOT NULL, 
Position int NOT NULL,
PRIMARY KEY (StoreID, CriteriaNo)
);
GO
INSERT INTO #T(StoreID, CriteriaNo, Result, Position)
SELECT '0001', 9, 10, 1 UNION ALL 
SELECT '0002', 9, 12, 2 UNION ALL 
SELECT '0001', 10, 5, 1 UNION ALL
SELECT '0002', 10, 6, 2;
GO
-- dynamic
DECLARE @sql nvarchar(MAX), @Cols nvarchar(max);

SELECT @Cols = (select ', ' + 'MAX(case when CriteriaNo = ' + CONVERT(varchar(20), CriteriaNo) + 
' then Result else 0 end) AS CriteriaNo' + CONVERT(varchar(20), CriteriaNo) + 
', MAX(case when CriteriaNo = ' + CONVERT(varchar(20), CriteriaNo) + 
' then Position else 0 end) AS CriteriaPosition' + CONVERT(varchar(20), CriteriaNo)
from (select distinct CriteriaNo from #T) X ORDER By CriteriaNo 
FOR XML PATH(''))

SET @sql = 'SELECT StoreID' + @Cols + ', SUM(Result) as PerformancePrint
FROM #T
GROUP BY StoreID'

EXECUTE(@sql)

See also my TechNet WiKi article on this same topic with more code samples

T-SQL: Dynamic Pivot on Multiple Columns

*** Remember, if you have a SQL related question, try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum