Crosstabs over Pivots

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.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , . Bookmark the permalink.

2 Responses to Crosstabs over Pivots

  1. Gary says:

    You claim it’s easier to add a column using the crosstab method, then immediately prove that false by making a cut and paste typo. How is adding [NE] to the pivot more compicated?

    Like

    • way0utwest says:

      I’m not sure where a typo on moving code to this piece implies anything about the relative ease of either method. It’s a cut/paste mistake on my part, not a coding mistake as I was testing a few things.

      My opinion is that the crosstab is cleaner since I’m adding both the result set and the case in the same place. In a PIVOT I’d have to alter the column list (to get a column name I might want) and the PIVOT command.

      Like

Comments are closed.