SQL Server UNPIVOT Examples: UNPIVOT Columns to Rows with Built-In UNPIVOT Operator or UNION ALL Statements

In a previous post, we discussed UNPIVOT columns to rows by hardcoding a list of column names to UNPIVOT.

In this post, we'll create a stored procedure that implements Dynamic PIVOT so we no longer have to maintain a hardcoded list of column names within the UNPIVOT operator.

You can use this DB Fiddle to follow along with SQL Server Dynamic UNPIVOT Examples in this post.

Sample Data to UNPIVOT Dynamically

Let’s create some sample data to demonstrate how to use the UNPIVOT operation to convert columns to rows. We will create a SkillQuiz table which has a Coder, SQL, and Pythoncolumns. SQL and Pythoncolumns store each coder's Score.

Create SkillQuiz Table to Demonostrate the UNPIVOT Operation

CREATE TABLE SkillQuiz (
	Coder VARCHAR(300)
	,SQL int
	,Python int
	);

INSERT INTO SkillQuiz (Coder,SQL,Python)
VALUES
    ('Bob', 95, 80),
    ('Emily', 98, 70);

Coder	SQL	Python
Bob	95	80
Emily	98	70

Dynamically UNPIVOT Columns to ROWS with the Built-In UNPIVOT Operator

Below is a static SQL query to UNPIVOT and consolidate SQL and Python columns to a single column Subject.

Static UNPIVOT Query

SELECT Coder
	,Subject
	,Score
FROM SkillQuiz
UNPIVOT(Score FOR Subject IN (
    -- We want to generate this UNPIVOT list dynamically
			SQL
			,Python
			)) AS Unpivoted_Table
ORDER BY Coder
	,Score DESC

We can generate the UNPIVOT list dynamically with the following query.

We can query sys.columns to dynamically find out column names we want to UNPIVOT and then use STRING_AGG() function to generate a comma-delimited PIVOT list. Note that we use QUOTENAME() function to return a Unicode string with delimiters added to make the string a valid SQL Server delimited identifier. This function is useful to prevent SQL injection.

Generate Comma-Delimited UNPIVOT List Dynamically with STRING_AGG() Function

SELECT STRING_AGG(QUOTENAME(name), ',') AS UnpivotList
FROM sys.columns
WHERE [object_id] = OBJECT_ID('SkillQuiz')
	AND name <> 'Coder';

UnpivotList
[Python],[SQL]

Now we know how to generate an UNPIVOT list dynamically, we can create a stored procedure that takes 3 parameters.

The first parameter, @PivotTable, takes the name of the Pivot table we want to UNPIVOT.

The second parameter, @KeyColumn, takes the name of the key column that we do NOT want to UNPIVOT.

The first and second parameters are used together to query sys.columns and produce a comma-delimited UNPIVOT column list with the help of STRING_AGG() function.

The third parameter, @UnPivotColumnName, takes the perferred name of the single UNPIVOT column which will hold consolidated values from UNPIVOTed columns (Python and SQL in our simple example).

The stored procedure outputs the Dynamic PIVOT SQL generated and then executes the query.

Stored Procedure to Dynamic UNPIVOT Columns to Rows

CREATE PROCEDURE dbo.UnPivotColumnsToRowsDynamically
  @PivotTable NVARCHAR(255),
  @KeyColumn NVARCHAR(255),
  @UnPivotColumnName NVARCHAR(255)
AS
BEGIN

DECLARE @UnPivotList NVARCHAR(255) = (
SELECT STRING_AGG(QUOTENAME(name), ',')
FROM sys.columns
WHERE [object_id] = OBJECT_ID(@PivotTable)
	AND name <> @KeyColumn
	);

DECLARE @UnPivotSQL NVARCHAR(MAX) = N'
SELECT Coder
	,Subject
	,Score
FROM SkillQuiz
UNPIVOT(Score FOR ' + @UnPivotColumnName + ' IN (
	' + @UnPivotList + '
			)) AS Unpivoted_Table
ORDER BY Coder
	,Score DESC
';

-- Output Dynamic UNPIVOT SQL for debugging
SELECT(@UnPivotSQL) AS UnPivotSQL;

-- Execute Dynamic UNPIVOT SQL
EXEC(@UnPivotSQL);

END

Now we will invoke our Dynamic UNPIVOT stored procedure and we should get the same result as our Static UNPIVOT SQL Query.

Invoke Stored Procedure to Dyanmically UNPIVOT Columns to Rows

DECLARE @PivotTable NVARCHAR(255) = N'SkillQuiz';
DECLARE @KeyColumn NVARCHAR(255) = N'Coder';
DECLARE @UnPivotColumnName SYSNAME = N'Subject';

EXEC dbo.UnPivotColumnsToRowsDynamically @PivotTable, @KeyColumn, @UnPivotColumnName;

The stored procedure generates the following Dynamic UNPIVOT query and return the same query results as our Static UNPIVOT query as expected.

Dynamic UNPIVOT SQL Script Generated with 2 Columns to UNPIVOT

SELECT Coder
	,Subject
	,Score
FROM SkillQuiz
UNPIVOT(Score FOR Subject IN (
	[Python],[SQL]
			)) AS Unpivoted_Table
ORDER BY Coder
	,Score DESC

Coder	Subject	Score
Bob	SQL	95
Bob	Python	80
Emily	SQL	98
Emily	Python	70

Now it's time to further test our code by altering the SkillQuiz table and adding a new Java column. Insert 2 new quiz records with Java score.

Add a New Column to SkillQuiz Table

ALTER TABLE SkillQuiz
ADD Java int;

INSERT INTO SkillQuiz (Coder,SQL,Python,Java)
VALUES
    ('Ryan', 95, 90, 70),
    ('Josh', 60, 77, 100);

This is what we have in our SkillQuiz table now.

Coder	SQL	Python	Java
Bob	95	80	NULL
Emily	98	70	NULL
Ryan	95	90	70
Josh	60	77	100

Let's execute the stored procedure again to generate Dynamic UNPIVOT query.

Invoke Stored Procedure to Dyanmically UNPIVOT Columns to Rows

DECLARE @PivotTable NVARCHAR(255) = N'SkillQuiz';
DECLARE @KeyColumn NVARCHAR(255) = N'Coder';
DECLARE @UnPivotColumnName SYSNAME = N'Subject';

EXEC dbo.UnPivotColumnsToRowsDynamically @PivotTable, @KeyColumn, @UnPivotColumnName;

The stored procedure detected the new Java column and produced the following Dynamic UNPIVOT SQL script. Both newly-inserted records were UNPIVOTED successfully.

Dynamic UNPIVOT SQL Script Generated with 3 Columns to UNPIVOT

SELECT Coder
	,Subject
	,Score
FROM SkillQuiz
UNPIVOT(Score FOR Subject IN (
	[Java],[Python],[SQL]
			)) AS Unpivoted_Table
ORDER BY Coder
	,Score DESC

Coder	Subject	Score
Bob	SQL	95
Bob	Python	80
Emily	SQL	98
Emily	Python	70
Josh	Java	100
Josh	Python	77
Josh	SQL	60
Ryan	SQL	95
Ryan	Python	90
Ryan	Java	70