SQL Server Dynamic PIVOT: Create Stored Procedure to Dynamically PIVOT Rows to Columns with Built-In PIVOT Operator.

In a previous post, we discuss using the built-in PIVOT operator to PIVOT rows into columns by providing a static list of values in a single column and pivot them to columns. The approach won't scale very well if new values are introduced into the PIVOT column on a regular basis. In this post, we'll discuss how to implement a Dynamic PIVOT solution in SQL Server.

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

Sample Data to PIVOT Dynamically

Let’s create some sample data to demonstrate how to use the PIVOT operation to turn rows into columns. We will create a SkillQuiz table which has a Coder name, Subject, and Scorecolumns.

Create SkillQuiz Table to Demonostrate Dynamic PIVOT
CREATE TABLE SkillQuiz(
  [Coder] VARCHAR(50),
  [Subject] VARCHAR(50),
  [Score] INT
)

INSERT INTO SkillQuiz VALUES
('Bob','SQL',95),
('Bob','Python',80),
('Emily','SQL',98),
('Emily','Python',90)

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

Dynamically PIVOT Rows to Columns with the Built-In PIVOT Operator

Below is a static SQL query to PIVOT 2 values in the Subject column to 2 column headers.

Static PIVOT Query
SELECT *
FROM (
	SELECT [Coder]
		,[Subject]
		,[Score]
	FROM SkillQuiz
	) AS SkillQuizResults
    -- We want to parameterize the PIVOT column Subject so it can be changed dynamically
PIVOT(SUM([Score]) FOR [Subject] IN (
    -- We want to generate this PIVOT list dynamically
	Python,SQL
			)) AS PivotTable

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

We want a PIVOT list that has distinct subject values so we retrieve a distinct list first in a subquery before running STRING_AGG() function to generate a comma-delimited PIVOT list.

Generate Comma-Delimited PIVOT List Dynamically with STRING_AGG() Function
SELECT STRING_AGG(SUB.Subject, ',') AS PivotList
FROM (
	SELECT DISTINCT (Subject)
	FROM SKillQuiz
	) AS SUB

PivotList
Python,SQL

Now we know how to generate a PIVOT list dynamically, we can create a stored procedure that takes 2 parameters.

The first parameter, @PivotColumn, can take the name of the PIVOT column which is Subject in our example.

The second parameter, @PivotList , can take the comma-delimited PIVOT list which is Python,SQL in our example.

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

Stored Procedure to Dynamic PIVOT Rows to Columns
CREATE PROCEDURE dbo.PivotRowsToColumnsDynamically
  @PivotColumn NVARCHAR(100),
  @PivotList NVARCHAR(255)
AS
BEGIN

DECLARE @PivotSQL NVARCHAR(MAX) = N'
SELECT *
FROM (
	SELECT [Coder]
		,[Subject]
		,[Score]
	FROM SkillQuiz
	) AS SkillQuizResults
PIVOT(SUM([Score]) FOR [' + @PivotColumn + '] IN (
	' + @PivotList + '
			)) AS PivotTable
';

-- Output Dynamic PIVOT SQL for debugging
SELECT(@PivotSQL) AS PivotSQL;

-- Execute Dynamic PIVOT SQL
EXEC(@PivotSQL);

END

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

Invoke Stored Procedure to Dyanmically PIVOT Rows to Columns
DECLARE @ColumnToPivot NVARCHAR(100) = 'Subject';

-- Generate a dynamic PIVOT list
DECLARE @ColumnHeaders NVARCHAR(255) = (
		SELECT STRING_AGG(SUB.Subject, ',')
		FROM (
			SELECT DISTINCT (Subject)
			FROM SKillQuiz
			) AS SUB
		);

EXEC dbo.PivotRowsToColumnsDynamically @ColumnToPivot, @ColumnHeaders;

Coder	Python	SQL
Bob	80	95
Emily	90	98

Now let's insert a few more rows into SkillQuiz table. Notice that we have introduced 3 new values (Java, Scala, R) into the Subject column.

INSERT INTO SkillQuiz VALUES
('Bob','Java',96),
('Bob','Scala',86),
('Bob','R',86),
('Emily','Java',91),
('Emily','Scala',91),
('Emily','R',86);

SELECT *
FROM SkillQuiz

Coder	Subject	Score
Bob	SQL	95
Bob	Python	80
Emily	SQL	98
Emily	Python	90
Bob	Java	96
Bob	Scala	86
Bob	R	86
Emily	Java	91
Emily	Scala	91
Emily	R	86

Now we invoke our stored procedure again to verify it can handle 3 new values (Java, Scala, R) introduced in the Subject column.

Invoke Stored Procedure to Dyanmically PIVOT Rows to Columns
DECLARE @ColumnToPivot NVARCHAR(100) = 'Subject';

-- Generate a dynamic PIVOT list
DECLARE @ColumnHeaders NVARCHAR(255) = (
		SELECT STRING_AGG(SUB.Subject, ',')
		FROM (
			SELECT DISTINCT (Subject)
			FROM SKillQuiz
			) AS SUB
		);

EXEC dbo.PivotRowsToColumnsDynamically @ColumnToPivot, @ColumnHeaders;

The stored procedure generates the following PIVOT query and return the following query results.

Dynamic PIVOT Query Generated. PIVOT Column: Subject
SELECT *
FROM (
	SELECT [Coder]
		,[Subject]
		,[Score]
	FROM SkillQuiz
	) AS SkillQuizResults
PIVOT(SUM([Score]) FOR [Subject] IN (
	Java,Python,R,Scala,SQL
			)) AS PivotTable

Coder	Java	Python	R	Scala	SQL
Bob	96	80	86	86	95
Emily	91	90	86	91	98

Now let's invoke the stored procedure again. This time we choose Coder as the PIVOT column and we dynamically generate a PIVOT list of coder.

Invoke Stored Procedure to Dyanmically PIVOT Values in Coder Column
DECLARE @ColumnToPivot NVARCHAR(100) = 'Coder';
DECLARE @ColumnHeaders NVARCHAR(255) = (
		SELECT STRING_AGG(SUB.Coder, ',')
		FROM (
			SELECT DISTINCT (Coder)
			FROM SKillQuiz
			) AS SUB
		);

EXEC dbo.PivotRowsToColumnsDynamically @ColumnToPivot, @ColumnHeaders;

The stored procedure generates the following PIVOT query and return the following query results.

Dynamic PIVOT Query Generated. PIVOT Column: Coder
SELECT *
FROM (
	SELECT [Coder]
		,[Subject]
		,[Score]
	FROM SkillQuiz
	) AS SkillQuizResults
PIVOT(SUM([Score]) FOR [Coder] IN (
	Bob,Emily
			)) AS PivotTable

Subject	Bob	Emily
Java	96	91
Python	80	90
R	86	86
Scala	86	91
SQL	95	98

Sample Data to PIVOT Dynamically​

Dynamically PIVOT Rows to Columns with the Built-In PIVOT Operator​

Sample Data to PIVOT Dynamically

Dynamically PIVOT Rows to Columns with the Built-In PIVOT Operator