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

Is there a built-in function in SQL Server to unpivot non-normalized data and convert columns to rows just like you could do easily in Microsoft Excel? The answer is yes!

In this post, we'll use the built-in UNPIVOT operation to normalize data by converting columns to rows. We'll also cover an alternative approach to UNPIVOT data using multiple UNION ALL statements.

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

Sample Data to UNPIVOT

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),
    ('Josh', 60, NULL);

Coder	SQL	Python
Bob	95	80
Emily	98	70
Josh	60	NULL

UNPIVOT Columns to Rows with the Built-In UNPIVOT Operator

The following TSQL script utilizes the built-in UNPIVOT operator which takes multiple columns (SQL, Python) provided in the IN keyword and consolidates them into a single column (Subject).

Unpivot Columns to Rows with Built-In UNPIVOT Operator

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

info

Notice that the UNPIVOT operator takes care of NULL value and will not display it in the UNPIVOT results.

In our example, we do not see Josh's Python score because the value is NULL.

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

UNPIVOT Columns to Rows with Multiple UNION ALL Statements

An alternative way of doing an UNPIVOT is to use UNION ALL statements instead to consolidate multiple columns (SQL, Python) into a single column (Subject).

The following TSQL script uses 2 SELECT statements and 1 UNION ALL statement.

The first SELECT statement unpivots SQL column to row. It selects Coder column, adds a new Subject column with value set to SQL, and adds a new Score column with value set to the value stored in the SQL column.

The second SELECT statement unpivots Python column to row. It selects Coder column, adds a new Subject column with value set to Python, and adds a new Score column with value set to the value stored in the Python column.

We then use a UNION ALL to append the result of the second SELECT statement to the result of the first SELECT statement.

Unpivot Columns to Rows with UNION ALL

WITH SkillQuizUnpivot
AS (
	SELECT Coder
		,'SQL' AS Subject
		,SQL AS Score
	FROM SkillQuiz

	UNION ALL

	SELECT Coder
		,'Python'
		,Python
	FROM SkillQuiz
	)
SELECT *
FROM SkillQuizUnpivot
WHERE Score IS NOT NULL
ORDER BY Coder
	,Score DESC

info

Notice that we have to add a WHERE clause to filter out NULL Score value if using UNION ALL approach to UNPIVOT columns to rows.

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

There comes a business requirement of adding Java to skill quiz for coders. We alter the table to add a new Java column to keep track of coder's score. We then add a new coder that has taken a Java skill quiz.

Add Java Column and Insert a New Coder Record

ALTER TABLE SkillQuiz
ADD Java int;

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

This is what we have in the SkillQuiz table after inserting the new row.

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

We have to add Java to the IN keyword within UNPIVOT section in order for the query to unpivot this new column.

Add Java Column to IN Keyword Within UNPIVOT Section

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

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

Similarly, we have to add 1 more SELECT statement and 1 more UNION ALL statement to accommodate the new Java column if we chose the alternative way to UNPIVOT data without using the built-in UNPIVOT operator.

UNION ALL Another SELECT Statement to UNPIVOT the New Java Column Added

WITH SkillQuizUnpivot
AS (
	SELECT Coder
		,'SQL' AS Subject
		,SQL AS Score
	FROM SkillQuiz

	UNION ALL

	SELECT Coder
		,'Python'
		,Python
	FROM SkillQuiz

	UNION ALL

	SELECT Coder
		,'Java'
		,Java
	FROM SkillQuiz
	)
SELECT *
FROM SkillQuizUnpivot
WHERE Score IS NOT NULL
ORDER BY Coder
	,Score DESC

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

UNPIVOT Conlusion

Both UNPIVOT techniques discussed for presenting multiple columns as rows work well if you have a limited number of known columns.

We demonstrated the potential maintenance issue by simply adding a new column into the table. We had to keep the hardcoded list within the UNPIVOT operator up to date and keep adding new UNION ALL statements.

Both of these UNPIVOT solutions won't scale very well if your database schema is evolving and your non-normalized table gets wider on a regular basis. In cases like this, you may need to leverage Dynamic SQL technique to build UNPIVOT query dynamically.