SQL Server PIVOT Examples: PIVOT Rows to Columns with Built-In PIVOT Operator or CASE Statements

Is there a built-in function in SQL Server to create a pivot table which pivots data from rows into columns just like you could do easily in Microsoft Excel? The answer is yes!

In this post, we'll leverage the built-in PIVOT operation to generate a pivot table so we can display query results in a more user-friendly way requested by busienss users. The PIVOT operation in Microsoft SQL Server can take values in a single column and show these values in column headers instead.

We'll also review another approach to PIVOT data using multiple CASE statements.

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

Sample Data to PIVOT

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

Create SkillQuiz Table to Demonostrate the PIVOT Operation

CREATE TABLE SkillQuiz(
  [Coder] VARCHAR(50),
  [Subject] VARCHAR(50),
  [Score] INT
)

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

Coder	Subject	Score
Bob	SQL	95
Bob	Python	80
Bob	Java	77
Emily	SQL	98
Emily	Python	90
Emily	Java	100

PIVOT Rows to Columns with the Built-In PIVOT Operator

We would like to take values in the Subject column and create a separate column for each subject.

In the FOR keyword, we tell the PIVOT operator that we would like to convert values in Subject column from rows into columns.

In the IN keyword, we list distinct values (SQL, Python, Java) we would like to add to the pivot table's column headers.

It is required for the PIVOT operator to have an aggregate function. In this script, we have used SUM function to aggregate the values from the Score column in the original table that contained each particular Coder/Subject combination.

Pivot Values in the Subject Column from Rows to Columns

SELECT *
FROM (
	SELECT [Coder]
		,[Subject]
		,[Score]
	FROM SkillQuiz
	) AS SkillQuizResults -- Source table to be pivoted
PIVOT(SUM([Score]) FOR [Subject] IN (
			SQL
			,Python
			,Java
			)) AS PivotTable

Coder	SQL	Python	Java
Bob	95	80	77
Emily	98	90	100

Notice that we provided hardcoded values in the list for the IN operator. If we insert two more records with a new subject JavaScript, we will have to manually add JavaScript to the hardcoded list!

Insert 2 new rows with JavaScript subject

INSERT INTO SkillQuiz VALUES
('Bob','JavaScript',85),
('Emily','JavaScript',70)

Coder	Subject	Score
Bob	SQL	95
Bob	Python	80
Bob	Java	77
Emily	SQL	98
Emily	Python	90
Emily	Java	100
Bob	JavaScript	85
Emily	JavaScript	70

Here we add the new subject JavaScript to the hardcoded list so it can show up as a new column header.

Add JavaScript subject to the hardcoded list

SELECT *
FROM (
	SELECT [Coder]
		,[Subject]
		,[Score]
	FROM SkillQuiz
	) AS SkillQuizResults -- Source table to be pivoted
PIVOT(SUM([Score]) FOR [Subject] IN (
			SQL
			,Python
			,Java
			,JavaScript
			)) AS PivotTable

Coder	SQL	Python	Java	JavaScript
Bob	95	80	77	85
Emily	98	90	100	70

This solution is fine if you're just performing some one-off ad-hoc analysis or will have a fixed set of values in the Subject column.

• It's not maintainable if new subjects are getting added in a regular basis. You would have to update the hardcoded list every time a new subject is getting added.
• It's not flexible if there is a requirement to pivot a different column for a different report. You would have to duplicate similar code and change it slightly. The following example modify the code slightly to apply PIVOT operation to convert values in Coder column into column headers.

Pivot Values in the Coder Column from Rows to Columns

SELECT *
FROM (
	SELECT [Coder]
		,[Subject]
		,[Score]
	FROM SkillQuiz
	) AS SkillQuizResults -- Source table to be pivoted
PIVOT(SUM([Score]) FOR [Coder] IN (
			Bob
			,Emily
			)) AS PivotTable

Subject	Bob	Emily
Java	77	100
JavaScript	85	70
Python	80	90
SQL	95	98

PIVOT Rows to Columns with Multiple CASE Statements

Another approach to pivot rows to columns is using multiple CASE statements.

Below is a SQL script that uses 4 CASE statements to PIVOT Subject column's values from rows to column headers. Note that we can use MAX, MIN, or SUM aggregate function because we only have 1 score value for each Coder/Subject combination.

Pivot Subject Column with Multiple CASE Statements

SELECT Coder
	,MAX(CASE Subject
			WHEN 'SQL'
				THEN Score
			ELSE 0
			END) AS SQL
	,MAX(CASE Subject
			WHEN 'Python'
				THEN Score
			ELSE 0
			END) AS Python
	,MAX(CASE Subject
			WHEN 'Java'
				THEN Score
			ELSE 0
			END) AS Java
	,MAX(CASE Subject
			WHEN 'JavaScript'
				THEN Score
			ELSE 0
			END) AS JavaScript
FROM SkillQuiz
GROUP BY Coder

Coder	SQL	Python	Java	JavaScript
Bob	95	80	77	85
Emily	98	90	100	70

Below is a SQL script that uses 2 CASE statements to PIVOT Coder column's values from rows to column headers. Note that we can use MAX, MIN, or SUM aggregate function because we only have 1 score value for each Coder/Subject combination.

Pivot Coder Column with Multiple CASE Statements

SELECT Subject
	,MAX(CASE Coder
			WHEN 'Bob'
				THEN Score
			ELSE 0
			END) AS Bob
	,MAX(CASE Coder
			WHEN 'Emily'
				THEN Score
			ELSE 0
			END) AS Emily
FROM SkillQuiz
GROUP BY Subject

Subject	Bob	Emily
Java	77	100
JavaScript	85	70
Python	80	90
SQL	95	98

PIVOT Conlusion

Both PIVOT techniques discussed for taking values in a single column and split it out to multiple columns work well if you have a static list of values to PIVOT.

We demonstrated the potential maintenance issue by introducing a new value into the Subject PIVOT column. We had to keep the hardcoded list within the PIVOT operator up to date and keep adding new CASE statements whenever new values are introduced into the PIVOT column.

Both of these PIVOT solutions won't scale very well if new values are showing up in the PIVOT column on a regular basis. In cases like this, you may need to leverage Dynamic SQL technique to build PIVOT query dynamically to reduce maintenance overhead.