Tag: T-SQL

I wrote about a basic PIVOT query recently. It’s an interesting way to write a query and turn row data into columns. That’s handy, and lots of people have a need for it. However I’d never used PIVOT in production code. I’ve always written a crosstab query instead.

If I look back at the query I wrote, it looks like this:

select
    *
  from
    ( select
          team
        , opponent
        , teamscore
        from
          scores results
    ) as rawdata 

pivot

 ( avg(teamscore) for [Opponent] in ( [KC], [OAK], [SD] )

 ) as pivotresults;

This gives me these results:

team   KC   OAK  SD

DEN    31   35   24

However I could also write this query:

select 
    'team' = team
,   'KC' = sum( case when Opponent = 'KC' then TeamScore else 0 end) / 2
,   'OAK' = sum( case when Opponent = 'OAK' then TeamScore else 0 end) / 2
,   'SD' = sum( case when Opponent = 'SD' then TeamScore else 0 end) / 2
 from scores s
 where team = 'DEN'
 group by team

That gives me the same results.  The AVG item gets tricky as can don’t want zeros to be included in results. If there were a dynamic number of scores, I’d have to write a few subqueries to solve this issue.

Which one is more clear and easier to understand? That’s a good debate. However I will say that if I need to add a column, I think it’s easier to do so in the crosstab.

select 
    'team' = team
,   'KC' = sum( case when Opponent = 'KC' then TeamScore else 0 end) / 2
,   'OAK' = sum( case when Opponent = 'OAK' then TeamScore else 0 end) / 2
,   'SD' = sum( case when Opponent = 'SD' then TeamScore else 0 end) / 2
,   'NE' = sum( case when Opponent = 'NE' then TeamScore else 0 end)
 from scores s
 where team = 'DEN'
 group by team
;

Here’s the PIVOT:

 select
    *
  from
    ( select
          team
        , opponent
        , teamscore
        from
          scores results
    ) as rawdata
  pivot 
   ( avg(teamscore) for [Opponent] in ( [KC], [OAK], [SD], [NE] ) 
   ) as pivotresults;

I’ll also point out that Jeff Moden has written a few articles on this subject (Part 1 and Part 2) and his performance analysis shows that a crosstab performs better in almost all cases.

I wouldn’t recommend one over the other. You need to test them both at larger than expected data sets to determine which one works in your situation.

I’ve been working on some skills, trying to grow some of my T-SQL, and started to mess with the PIVOT operator. This is a T-SQL construct that helps you turn row data into column data. It’s part of the SELECT query, and comes about in the FROM clause. There is an article on MSDN to help you understand this.

Note: Performance of PIVOT can be challenging. Jeff Moden has a great piece on Cross Tabs and PIVOT. It is worth a read to understand the limitations and issues with PIVOT.

Setup

I’ve got some sample data I’ve put together. In this case, I have a set of data from the 2013 NFL season, with the scores for the Denver Broncos.

CREATE TABLE Scores
(
Team varchar(3)
, Opponent varchar(3)
, gamedate datetime
, TeamScore int
, Oppscore int 
);
go

INSERT INTO scores (team, opponent, gamedate, teamscore, oppscore)
values
    ( 'DEN', 'BAL', '2013-9-5', 49, 27)
,   ( 'DEN', 'NYG', '2013-9-15', 41, 23)
,   ( 'DEN', 'OAK', '2013-9-23', 37, 21)
,   ( 'DEN', 'PHI', '2013-9-29', 52, 20)
,   ( 'DEN', 'DAL', '2013-10-6', 51, 48)
,   ( 'DEN', 'JAX', '2013-10-13', 35, 19)
,   ( 'DEN', 'IND', '2013-10-20', 33, 39)
,   ( 'DEN', 'WAS', '2013-10-27', 45, 21)
,   ( 'DEN', 'SD', '2013-11-10', 28, 20)
,   ( 'DEN', 'KC', '2013-11-17', 27, 17)
,   ( 'DEN', 'NE', '2013-11-24', 31, 34)
,   ( 'DEN', 'KC', '2013-12-1', 35, 28)
,   ( 'DEN', 'TEN', '2013-12-8', 51, 28)
,   ( 'DEN', 'SD', '2013-12-12', 20, 27)
,   ( 'DEN', 'HOU', '2013-12-22', 37, 13)
,   ( 'DEN', 'OAK', '2013-12-29', 34, 14)
;
go

The Problem

I want to get an average score for the Broncos for each of their opponents. They had 13 opponents in 16 games, with 3 teams being played twice. However, I want to see the data sideways, but I’m only going to show the scores for the opponents with multiple games. In this case, I know the teams are “KC”, “OAK’, and “SD”.

In other words, I want to see:

Team     KC    OAK   SD

DEN      X     Y     Z

I want the real averages for the games in question. I could include the other teams, but let’s leave this alone. 

Note that in my results I’ve limited things to Denver. I forgot to include the WHERE clause when I rewrote this post, but I had limited the winning team to DEN.

The Query

There are a couple parts to the query. First, there’s the column list, in this case, it’s a SELECT *. We’ll fix this as I dislike the asterisk, but let’s leave that for now. That means we have:

select
    *

Now, we need a FROM clause. The first part of the FROM clause will be my source data. This is a normal select, in this case, we’ have:

select
          team
        , opponent
        , teamscore
        from scores
          results

We need to embed this in parenthesis, so that gives us this:

select
    *
  from
    ( select
          team
        , opponent
        , teamscore
        from scores
          results
    ) as rawdata

So far this is a normal query. Not it’s time to PIVOT things. First we add a PIVOT clause to the end of the statement. The outline looks like this (with our query):

select * from ( select team , opponent , teamscore from scores results ) as rawdata

PIVOT

( aggregate(col) for var in ([col1], [col2], [col3]

) as pivotalias

The PIVOT clause includes one (and only one) aggregate. You choose the aggregate and a column. In my example, I’ll be averaging the scores for the teamscore column.

The next part of the clause is a pivot column, on which we are looing for data. This column must be in the  select list for the first part of the query. Next we have the “IN” clause, in which we list the various values that we are moving from rows to columns.

In this case, I’m looking to move the divisional opponents to columns rather than rows. I’m pivoting on the Opponent column and looking for the AFC West opponents. Those teams are OAK, SD, and KC. That makes my query look like:

select * from ( select team , opponent , teamscore from scores results ) as rawdata

pivot

( avg(teamscore) for [Opponent] in ( [KC], [OAK], [SD] )

) as pivotresults;

This will give me these results:

team   KC   OAK  SD

DEN    31   35   24

I have the average scores for opponents as columns, not rows.

Technically, I should include DEN as an opponent so I can actually build a proper grid. If I do that, I get:

select
    *
  from
    ( select
          team
        , opponent
        , teamscore
        from
          scores results
    ) as rawdata
  pivot 
   ( avg(teamscore) for [Opponent] in ( [KC], [OAK], [SD], [DEN] ) 
   ) as pivotresults;

And these results.

team   KC   OAK  SD  DEN

DEN    31   35   24  NULL

Note that this gives me a NULL. I can handle that with an ISNULL, COALESCE, etc.

If I add more data for the other teams, then I’ll get this:

INSERT INTO scores (team, opponent, gamedate, teamscore, oppscore)
values
    ( 'KC', 'DEN', '2013-9-5', 49, 27)
,   ( 'KC', 'SD', '2013-11-24', 38, 41)
,   ( 'OAK', 'DEN', '2013-9-23', 21, 37)
,   ( 'KC', 'DEN', '2013-9-29', 52, 20)
,   ( 'KC', 'SD', '2013-12-29', 24, 27)
,   ( 'KC', 'OAK', '2013-10-13', 24, 7)
,   ( 'KC', 'OAK', '2013-12-15', 56, 31)
,   ( 'OAK', 'DEN', '2013-10-27', 45, 21)
,   ( 'OAK', 'SD', '2013-10-6', 27, 17)
,   ( 'OAK', 'SD', '2013-10-27', 45, 21)
,   ( 'OAK', 'KC', '2013-10-13', 7, 24)
,   ( 'OAK', 'KC', '2013-12-15', 31, 56)
,   ( 'SD', 'DEN', '2013-11-10', 20, 28)
,   ( 'SD', 'DEN', '2013-11-10', 20, 28)
,   ( 'SD', 'OAK', '2013-10-6', 17, 27)
,   ( 'SD', 'OAK', '2013-11-10', 20, 28)
,   ( 'SD', 'KC', '2013-11-24', 41, 38)
,   ( 'SD', 'KC', '2013-12-29', 27, 24)
;
go

And these results from the same query:

That’s pretty cool, though I’d argue that the data isn’t valuable for many people unless you’re trying to bet or analyze the performance of teams against each other.

As I mentioned at the top, this isn’t necessarily the best way to move data. Performance can be an issue, however there are people that find this to be an easier way to write code and understand what it’s doing.

I might argue that a cross tab can be just as easy to code and maintain, but you may feel the differently.