The SQL Server PIVOT Query | Segue Technologies

Written by Segue Technologies on April 30, 2014

For relational databases, normal SQL output will display data vertically. Meaning one row on top of the other. In many cases, displaying the information horizontally will look better, be more understandable, and make more sense. The SQL Server PIVOT operator is used to accomplish this.

SQL Server introduced the PIVOT query in SQL Server 2005. I discovered the SQL Server PIVOT operator when I was attempting to transpose my SQL query output to display in a grid. I needed to display a column’s unique value as column headers. In the past, I would transpose the data using a language like C# prior to binding it to a grid. The purpose of the PIVOT query is to rotate the output and display vertical data horizontally. These queries are also known as crosstab queries. The SQL Server PIVOT operator can be used to easily rotate/transpose your data. This is a very nice tool if the data values that you wish to rotate are not likely to change. An example of this would be States, Months of the year, Days of the Week, Sports Teams, and Military Ranks. The PIVOT concept is similar to the transpose feature in MS Excel where a column of information can be converted into a row of information.

The PIVOT operator can convert Table 1 into Table 2.

Table 1

Name	Month	Value
A	Jan	1
B	Jan	2
C	Jan	3
D	Jan	4
A	Feb	5
B	Feb	6
C	Feb	7
D	Feb	8
A	Mar	9
B	Mar	10
C	Mar	11
D	Mar	12
A	Apr	13
B	Apr	14
C	Apr	15
D	Apr	16

Table 2

Name	A	B	C	D
Jan	1	2	3	4
Feb	5	6	7	8
Mar	9	10	11	12
Apr	13	14	15	16

Notice that Table 2 is smaller and much easier to read and understand than Table 1.

Example 1:

In SQL Server 2008R2, I have an NFL database with all the game results. I want to see the average number of points each team scored over the years. To make things simple, I will look at the last 4 years for the NFC West only. To display the average, I will need an aggregate query like the one below.

SELECT Year, Team, AVG(PointsScored) as AvgPoints

FROM vPointsScored

WHERE Conference = ‘NFC’ and Division = ‘WEST’ and Year >= 2010

GROUP BY Year, Team

ORDER BY Year, Team;

The output will look like this:

Year	Team	AvgPoints
2010	49ers	19
2010	Cardinals	18
2010	Rams	18
2010	Seahawks	20
2011	49ers	24
2011	Cardinals	19
2011	Rams	12
2011	Seahawks	20
2012	49ers	26
2012	Cardinals	15
2012	Rams	18
2012	Seahawks	25
2013	49ers	24
2013	Cardinals	23
2013	Rams	21
2013	Seahawks	26

The PIVOT operator will allow me to display the 4 NFC West teams as column headers and each year will be a row header.

Year	49ers	Cardinals	Rams	Seahawks
2010	19	18	18	20
2011	24	19	12	20
2012	26	15	18	25
2013	24	23	21	26

The SQL Server PIVOT query will be this:

SELECT Year,

[49ers],

[Cardinals],

[Rams],

[Seahawks]

FROM

(SELECT Year, Team, PointsScored FROM vPointsScored WHERE Year >= 2010) as Points

PIVOT

(

AVG(PointsScored)

FOR Team in ([49ers],

[Cardinals],

[Rams],

[Seahawks])

)AS PivotTable;

The output will be this:

Year	49ers	Cardinals	Rams	Seahawks
2010	19	18	18	20
2011	24	19	12	20
2012	26	15	18	25
2013	24	23	21	26

Things to note:

The first part is specifying the five new column headers.
The second part is a normal select statement.
The third part is pivoting the data into the new column headers.
In the PIVOT query, the aggregation is done automatically.
1. We are averaging PointsScored and wish to display Year, Team, and PoinrtsScored. The query will automatically group by Year and Team.
We must know the Team names ahead of time.
1. The query must include the new Column Names which are 49ers, Cardinals, Rams, and Seahawks.
If the NFC West wanted to add another team, I would need to add that new team to the query.

Example 2:

Let’s view a list of all the NFC West 2013 Game Results.

Below is a normal SQL that will display the data in a vertical format.

SELECT Year, Week, Team, Score, Result

FROM vNFCWestGames

WHERE Year = 2013

ORDER BY Year, Week, Team;

Year	Week	Team	Score	Result
2013	1	49ers	34-28	(W)
2013	1	Cardinals	24-27	(L)
2013	1	Rams	27-24	(W)
2013	1	Seahawks	12-7	(W)
2013	2	49ers	3-29	(L)
2013	2	Cardinals	25-21	(W)
2013	2	Rams	24-31	(L)
2013	2	Seahawks	29-3	(W)
…	…	…	…	…
2013	17	49ers	23-20	(W)
2013	17	Cardinals	20-23	(L)
2013	17	Rams	9-27	(L)
2013	17	Seahawks	27-9	(W)
2013	18	49ers	23-20	(W)
2013	19	49ers	23-10	(W)
2013	19	Seahawks	23-15	(W)
2013	20	49ers	17-23	(L)
2013	20	Seahawks	23-17	(W)
2013	21	Seahawks	43-8	(W)

The data is correct, however, I want the Teams to be the Column Headers and the Year and Week to be the Row Headers.

Year	Week	49ers	Cardinals	Rams	Seahawks
2013	1	34-28	24-27	27-24	12-7
2013	2	3-29	25-21	24-31	29-3
2013	3	7-27	7-31	7-31	45-17

The difference between this data and Example 1 is there is no aggregate function needed. However, we will need one anyway. We can use the MIN or MAX aggregate that will return the same value. The PIVOT SQL query is very simple:

SELECT Year,

Week,

[49ers],

[Cardinals],

[Rams],

[Seahawks]

FROM

(SELECT Year, Week, Team, Score FROM dbo.vNFCWestGames WHERE Year = 2013) as Games

PIVOT

(MIN(Score)

FOR Team in ([49ers],

[Cardinals],

[Rams],

[Seahawks])

)AS PivotTable;

The result will be:

Year	Week	49ers	Cardinals	Rams	Seahawks
2013	1	34-28	24-27	27-24	12-7
2013	2	3-29	25-21	24-31	29-3
2013	3	7-27	7-31	7-31	45-17
2013	4	35-11	13-10	11-35	23-20
2013	5	34-3	22-6	34-20	28-34
2013	6	32-20	20-32	38-13	20-13
2013	7	31-17	22-34	15-30	34-22
2013	8	42-10	27-13	9-13	13-9
2013	9	NULL	NULL	21-28	27-24
2013	10	9-10	27-24	38-8	33-10
2013	11	20-23	27-14	NULL	41-20
2013	12	27-6	40-11	42-21	NULL
2013	13	23-13	21-24	13-23	34-7
2013	14	19-17	30-10	10-30	17-19
2013	15	33-14	37-34	27-16	23-0
2013	16	34-24	17-10	23-13	10-17
2013	17	23-20	20-23	9-27	27-9
2013	18	23-20	NULL	NULL	NULL
2013	19	23-10	NULL	NULL	23-15
2013	20	17-23	NULL	NULL	23-17
2013	21	NULL	NULL	NULL	43-8

Some things to note:

The Score value is a varchar and not a number.
We will need to aggragate the Score value.
We can simply use an aggragate like MIN or MAX that will return the original value.
Because Seattle played all the way to week 21, the Week row header must go to Week 21. The output will display NULL for empty data.

Example 3:

Let’s continue to format Example 2. In this example, I want each team’s score to include a Win or Loss result.

SELECT Year,

Week,

[49ers],

[Cardinals],

[Rams],

[Seahawks]

FROM

(Select Year, Week, Team, Result + ‘ ‘ + Score as Score from dbo.vNFCWestGames where Year = 2013) as Games

PIVOT

(MIN(Score)

FOR Team in ([49ers],

[Cardinals],

[Rams],

[Seahawks])

)AS PivotTable;

Year	Week	Arizona Cardinals	San Francisco 49ers	Seattle Seahawks	St. Louis Rams
2013	1	(L) 24-27	(W) 34-28	(W) 12-7	(W) 27-24
2013	2	(W) 25-21	(L) 3-29	(W) 29-3	(L) 24-31
2013	3	(L) 7-31	(L) 7-27	(W) 45-17	(L) 7-31
2013	4	(W) 13-10	(W) 35-11	(W) 23-20	(L) 11-35
2013	5	(W) 22-6	(W) 34-3	(L) 28-34	(W) 34-20
2013	6	(L) 20-32	(W) 32-20	(W) 20-13	(W) 38-13
2013	7	(L) 22-34	(W) 31-17	(W) 34-22	(L) 15-30
2013	8	(W) 27-13	(W) 42-10	(W) 13-9	(L) 9-13
2013	9	NULL	NULL	(W) 27-24	(L) 21-28
2013	10	(W) 27-24	(L) 9-10	(W) 33-10	(W) 38-8
2013	11	(W) 27-14	(L) 20-23	(W) 41-20	NULL
2013	12	(W) 40-11	(W) 27-6	NULL	(W) 42-21
2013	13	(L) 21-24	(W) 23-13	(W) 34-7	(L) 13-23
2013	14	(W) 30-10	(W) 19-17	(L) 17-19	(L) 10-30
2013	15	(W) 37-34	(W) 33-14	(W) 23-0	(W) 27-16
2013	16	(W) 17-10	(W) 34-24	(L) 10-17	(W) 23-13
2013	17	(L) 20-23	(W) 23-20	(W) 27-9	(L) 9-27
2013	18	NULL	(W) 23-20	NULL	NULL
2013	19	NULL	(W) 23-10	(W) 23-15	NULL
2013	20	NULL	(L) 17-23	(W) 23-17	NULL
2013	21	NULL	NULL	(W) 43-8	NULL

One thing to note is that the only difference is in the Select clause, Result is being concatenated with Score.

Example 4:

Let’s replace the NULLs with empty values.

SELECT Year,

Week,

ISNULL([49ers], ”) as ’49ers’,

ISNULL([Cardinals], ”) as ‘Cardinals’,

ISNULL([Rams], ”) as ‘Rams’,

ISNULL([Seahawks], ”) as ‘Seahawks’

FROM

(Select Year, Week, Team, Result + ‘ ‘ + Score as Score from dbo.vNFCWestGames where Year = 2013) as Games

PIVOT

(MIN(Score)

FOR Team in ([49ers],

[Cardinals],

[Rams],

[Seahawks])

)AS PivotTable;

Year	Week	49ers	Cardinals	Rams	Seahawks
2013	1	(W) 34-28	(L) 24-27	(W) 27-24	(W) 12-7
2013	2	(L) 3-29	(W) 25-21	(L) 24-31	(W) 29-3
2013	3	(L) 7-27	(L) 7-31	(L) 7-31	(W) 45-17
2013	4	(W) 35-11	(W) 13-10	(L) 11-35	(W) 23-20
2013	5	(W) 34-3	(W) 22-6	(W) 34-20	(L) 28-34
2013	6	(W) 32-20	(L) 20-32	(W) 38-13	(W) 20-13
2013	7	(W) 31-17	(L) 22-34	(L) 15-30	(W) 34-22
2013	8	(W) 42-10	(W) 27-13	(L) 9-13	(W) 13-9
2013	9			(L) 21-28	(W) 27-24
2013	10	(L) 9-10	(W) 27-24	(W) 38-8	(W) 33-10
2013	11	(L) 20-23	(W) 27-14		(W) 41-20
2013	12	(W) 27-6	(W) 40-11	(W) 42-21
2013	13	(W) 23-13	(L) 21-24	(L) 13-23	(W) 34-7
2013	14	(W) 19-17	(W) 30-10	(L) 10-30	(L) 17-19
2013	15	(W) 33-14	(W) 37-34	(W) 27-16	(W) 23-0
2013	16	(W) 34-24	(W) 17-10	(W) 23-13	(L) 10-17
2013	17	(W) 23-20	(L) 20-23	(L) 9-27	(W) 27-9
2013	18	(W) 23-20
2013	19	(W) 23-10			(W) 23-15
2013	20	(L) 17-23			(W) 23-17
2013	21				(W) 43-8

Some things to note:

The Data for the 4 teams are being displayed as empty strings if the Score is NULL.

In conclusion, the PIVOT operator is a nice tool when you need to display data on a web page or download data to an Excel Worksheet. This eliminates the steps needed to manually transpose and format your information. The more features and functionality that can be done at the database level, the better. This will certainly cut time off of implementation, troubleshooting, and maintenance.

Need Help? Contact us

Example 1:

Example 2:

Example 3:

Example 4:

Read Next