This article will go over how to transform distinct column values into columns and transform columns into distinct column values using the PIVOT and UNPIVOT function. There are 2 different ways to use these functions – static and dynamic. The static approach is used when the distinct columns or column values are known. If the query gets an extra unique column or value after the initial query then the new column or value will not reflect in the result set. Whereas the dynamic approach is the exact opposite, as there is no worry about new columns or values being added after the initial query.
First I’ll go over the static approach to explain how the functions are structured. Please note that these functions are available in SQL Server 2005 and later with a compatibility level of 90 or higher.
PIVOT Syntax – Static Approach
SELECT [non-pivoted column(s)], [pivoted column(s)] FROM ( Query ) [table alias] PIVOT ( <aggregation function>([column]) FOR [column that contains the values that will become the column headers] IN ( [pivoted columns] ) ) [pivot table alias] [optional order by];
UNPIVOT Syntax– Static Approach
SELECT [non un-pivoted column(s)], [un-pivoted column], [un-pivoted column value] FROM ( Query ) [table alias] UNPIVOT ( [column that contains un-pivoted column values] FOR [column that contains un-pivoted columns] IN ( [un-pivoted columns] ) ) [un-pivot] table alias] [optional order by];
This syntax and more complex examples can be found here.
Below are simple examples of both functions looking at employee sales per year.
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 |
CREATE TABLE dbo.tblPVT( EMPLOYEEID VARCHAR(50), FISCALYR INT, AMOUNT DECIMAL(18,2) ); INSERT INTO tblPVT ( EMPLOYEEID, FISCALYR, AMOUNT ) VALUES ( 'EMP01', 2014, 19000.00 ), ( 'EMP01', 2014, 28000.00 ), ( 'EMP01', 2014, 25005.00 ), ( 'EMP01', 2015, 12099.00 ), ( 'EMP01', 2013, 1225.00 ), ( 'EMP02', 2013, 1225.00 ), ( 'EMP02', 2013, 1225.00 ), ( 'EMP02', 2014, 1225.00 ), ( 'EMP02', 2015, 1225.00 ); SELECT FISCALYR, EMP01, EMP02 FROM tblPVT A PIVOT( SUM(AMOUNT) FOR EMPLOYEEID IN ( EMP01, EMP02 ) ) P; DROP TABLE dbo.tblPVT; |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
CREATE TABLE dbo.tblUNPVT( EMPLOYEEID VARCHAR(50), FY2012 DECIMAL(18,2), FY2013 DECIMAL(18,2), FY2014 DECIMAL(18,2) ); INSERT INTO tblUNPVT ( EMPLOYEEID, FY2012, FY2013, FY2014 ) VALUES ( 'EMP01', 25000, 23900, 30000 ), ( 'EMP02', 19000, 21100, 20000 ), ( 'EMP03', 21000, 22900, 22000 ); SELECT EMPLOYEEID, FISCALYR, AMOUNT FROM tblUNPVT A UNPIVOT ( AMOUNT FOR FISCALYR IN ( FY2012, FY2013, FY2014 ) ) U; DROP TABLE dbo.tblUNPVT; |
Dynamic PIVOT and UNPIVOT Query
To take a static query for both functions and make them dynamic, any hardcoded columns in the main SELECT and IN clause need to be moved into variables. Please note that if you’re using varchar variables to store the query, there is an 8000 character limit.
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 |
CREATE TABLE dbo.tblPVT( EMPLOYEEID VARCHAR(50), FISCALYR INT, AMOUNT DECIMAL(18,2) ); INSERT INTO tblPVT ( EMPLOYEEID, FISCALYR, AMOUNT ) VALUES ( 'EMP01', 2014, 19000.00 ), ( 'EMP01', 2014, 28000.00 ), ( 'EMP01', 2014, 25005.00 ), ( 'EMP01', 2015, 12099.00 ), ( 'EMP01', 2013, 1225.00 ), ( 'EMP02', 2013, 1225.00 ), ( 'EMP02', 2013, 1225.00 ), ( 'EMP02', 2014, 1225.00 ), ( 'EMP02', 2015, 1225.00 ); DECLARE @QUERY AS VARCHAR(MAX), @COL AS VARCHAR(MAX); --Get distinct values of the PIVOT Column SELECT @COL = ISNULL(@COL + ',','') + QUOTENAME(EMPLOYEEID) FROM (SELECT DISTINCT EMPLOYEEID FROM tblPVT) E; --Prepare the PIVOT query SET @QUERY = 'SELECT FISCALYR, ' + @COL; SET @QUERY += ' FROM tblPVT A' SET @QUERY += ' PIVOT(SUM(AMOUNT)' SET @QUERY += ' FOR EMPLOYEEID IN (' + @COL + ')) P;'; --Execute the Dynamic PIVOT Query EXEC(@QUERY); DROP TABLE dbo.tblPVT; |
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 |
CREATE TABLE dbo.tblUNPVT( EMPLOYEEID VARCHAR(50), FY2012 DECIMAL(18,2), FY2013 DECIMAL(18,2), FY2014 DECIMAL(18,2) ); INSERT INTO tblUNPVT ( EMPLOYEEID, FY2012, FY2013, FY2014 ) VALUES ( 'EMP01', 25000, 23900, 30000 ), ( 'EMP02', 19000, 21100, 20000 ), ( 'EMP03', 21000, 22900, 22000 ); DECLARE @QUERY AS VARCHAR(MAX), @COL AS VARCHAR(MAX); --Get table columns of the UNPIVOT distinct values SELECT @COL = ISNULL(@COL + ',','') + QUOTENAME(name) FROM ( SELECT c.Name FROM sys.columns c INNER JOIN sys.objects o ON o.object_id = c.object_id WHERE o.name = 'tblUNPVT' AND c.NAME <> 'EMPLOYEEID' ) E; --Prepare the UNPIVOT query SET @QUERY = 'SELECT EMPLOYEEID, FISCALYR, AMOUNT'; SET @QUERY += ' FROM tblUNPVT A' SET @QUERY += ' UNPIVOT(' SET @QUERY += ' AMOUNT FOR FISCALYR IN (' + @COL + ')) U;'; --Execute the Dynamic UNPIVOT Query EXEC(@QUERY); DROP TABLE dbo.tblUNPVT; |
These are basic examples of the static and dynamic PIVOT and UNPIVOT queries that can be extended to meet your needs.
