CASE vs Pivot

Question

Post reply

CASE vs Pivot

Christopher Stobbs

SSC-Insane

Points: 21098
More actions
August 26, 2008 at 8:05 am

#191820

HI All,
With this basic example is there a hard and fast better route for writing pivots:
[Code]
--Query1
SELECT
Col1,
[1] as [Jan],
[2] as [Feb],
[3] as [Mar],
[4] as [Apr],
[5] as [May],
[6] as [Jun],
[7] as [Jul],
[8] as [Aug],
[9] as [Sep],
[10] as [Oct],
[11] as [Nov],
[12] as [Dec]
FROM
(
SELECT Col1,DATEPART(m,date) as [Month],col2
FROM MyTable
WHERE
date > '2007-12-31'
AND
date < '2009-01-01'
) t
PIVOT (COUNT(col2) FOR [Month] IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) PVT
ORDER BY col1
--Query2
SELECT col1,
SUM(CASE WHEN DATEPART(m,date) = 1 THEN 1 ELSE 0 END) as [Jan],
SUM(CASE WHEN DATEPART(m,date) = 2 THEN 1 ELSE 0 END) as [Feb],
SUM(CASE WHEN DATEPART(m,date) = 3 THEN 1 ELSE 0 END) as [Mar],
SUM(CASE WHEN DATEPART(m,date) = 4 THEN 1 ELSE 0 END) as [Apr],
SUM(CASE WHEN DATEPART(m,date) = 5 THEN 1 ELSE 0 END) as [May],
SUM(CASE WHEN DATEPART(m,date) = 6 THEN 1 ELSE 0 END) as [Jun],
SUM(CASE WHEN DATEPART(m,date) = 7 THEN 1 ELSE 0 END) as [Jul],
SUM(CASE WHEN DATEPART(m,date) = 8 THEN 1 ELSE 0 END) as [Aug],
SUM(CASE WHEN DATEPART(m,date) = 9 THEN 1 ELSE 0 END) as [Sep],
SUM(CASE WHEN DATEPART(m,date) = 10 THEN 1 ELSE 0 END) as [Oct],
SUM(CASE WHEN DATEPART(m,date) = 11 THEN 1 ELSE 0 END) as [Nov],
SUM(CASE WHEN DATEPART(m,date) = 12 THEN 1 ELSE 0 END) as [Dev]
FROM MyTable
WHERE
date > '2007-12-31'
AND
date < '2009-01-01'
GROUP BY Col1
ORDER BY Col1
[/code]
Thanks
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
SQL-4-Life

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 1

From what I've seen, the PIVOT can be easier to read, but the CASE gives the same or better performance.

dmc-608719 SSCarpal Tunnel Points: 4663 More actions · Answer 2

I asked a similar question and someone provided me with this article from this site. It has some good performance insights to consider when using pivot or case statements.

http://www.sqlservercentral.com/articles/T-SQL/63681/[/url]

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 3

Thanks all for the feedback 🙂

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life