The SQL Server PIVOT Query

Share on FacebookTweet about this on TwitterShare on LinkedInGoogle+

Share on FacebookTweet about this on TwitterShare on LinkedInGoogle+

segue-blog-server-PIVOT-Query

 

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:

  1. The first part is specifying the five new column headers.
  2. The second part is a normal select statement.
  3. The third part is pivoting the data into the new column headers.
  4. 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.
  5. 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.
  6. 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:

  1. The Score value is a varchar and not a number.
  2. We will need to aggragate the Score value.
  3. We can simply use an aggragate like MIN or MAX that will return the original value.
  4. 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:

  1. 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