The PIVOT

Question

The PIVOT

Viewing 12 posts - 16 through 26 (of 26 total)

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

Carlo Romagnano SSC-Insane Points: 22668 More actions · Answer 1

Sean Lange (1/27/2015)
Carlo Romagnano (1/27/2015)
Strange, but the version with PIVOT is a bit slower than that with GROUP BY. See execution plan!
with c
as(
SELECT * FROM (VALUES
(1,2013,1,100)
,(1,2013,1,200)
,(1,2014,0,500)
,(1,2014,1,200)
,(2,2013,1,200)
,(2,2013,1,200)
,(2,2014,1,100)
,(2,2014,1,100)
) AS V([Customer],[Salesyear],[Complete],[Amount])
)
SELECT
*
FROM c
PIVOT( SUM(amount)
FOR Salesyear IN ( [2013], [2014] )
) AS pivotsales;
;with c
as(
SELECT * FROM (VALUES
(1,2013,1,100)
,(1,2013,1,200)
,(1,2014,0,500)
,(1,2014,1,200)
,(2,2013,1,200)
,(2,2013,1,200)
,(2,2014,1,100)
,(2,2014,1,100)
) AS V([Customer],[Salesyear],[Complete],[Amount])
)
SELECT
Customer,Complete
,sum(case when Salesyear = 2013 then Amount end) as [2013]
,sum(case when Salesyear = 2014 then Amount end) as [2014]
FROM c
GROUP BY Customer,Complete
Performance is one of the reasons I use cross tabs instead of PIVOT. I also find the syntax for pivot to be incredibly non-intuitive. The performance differences have been covered extensively by Jeff Moden. http://www.sqlservercentral.com/articles/T-SQL/63681/[/url]

+1

When I try to use PIVOT, there's always some "particular" case that PIVOT doesn't cover, So, I should return back to GROUP BY.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

Ed Wagner (1/27/2015)
I'm also someone who doesn't use pivot much at all. It seems like I have to look up the syntax nearly every time I use it.

Same here.

A matrix works so much better here. 😉

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

Carlo Romagnano (1/27/2015)
Raghavendra Mudugal (1/27/2015)
Carlo Romagnano (1/27/2015)
Strange, but the version with PIVOT is a bit slower than that with GROUP BY. See execution plan!
...
actually I was doing the same thing on v2012, but did not see any difference in the EP, (not sure if I am missing something) but would like to know more... thank you.
See "Estimated Subtree Cost" and "Estimated Number of Rows"

Execution plan won't tell you which option is slower. Both values that you mention are just estimates that can be completely wrong. To know which one is faster, you need to measure time, not execution plans.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Steve Jones - SSC Editor SSC Guru Points: 736172 More actions · Answer 4

Ed Wagner (1/27/2015)
I'm also someone who doesn't use pivot much at all. It seems like I have to look up the syntax nearly every time I use it.

Me too, though I look here: http://www.sqlservercentral.com/articles/T-SQL/63681/

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

Luis Cazares (1/27/2015)
Carlo Romagnano (1/27/2015)
Raghavendra Mudugal (1/27/2015)
Carlo Romagnano (1/27/2015)
Strange, but the version with PIVOT is a bit slower than that with GROUP BY. See execution plan!
...
actually I was doing the same thing on v2012, but did not see any difference in the EP, (not sure if I am missing something) but would like to know more... thank you.
See "Estimated Subtree Cost" and "Estimated Number of Rows"
Execution plan won't tell you which option is slower. Both values that you mention are just estimates that can be completely wrong. To know which one is faster, you need to measure time, not execution plans.

And certainly not the estimated execution plan. It is nearly worthless.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

SqlMel SSCrazy Points: 2901 More actions · Answer 6

SqlMel

SSCrazy

Points: 2901

January 27, 2015 at 10:06 am

#1773430

Good question.

Thanks!

---------------
Mel. 😎

Marcia J SSCertifiable Points: 5649 More actions · Answer 7

I got it right primarily because I had just finished reading an article by Robert Sheldon earlier today. He did a great job explaining this at a beginner level. The article is https://www.simple-talk.com/content/article.aspx?article=2107

xsevensinzx One Orange Chip Points: 25560 More actions · Answer 8

Good stuff.

Pivot was actually the first bit of SQL I learned. Unfortunately, the pivots I had to play with are the ones we will not speak of. But, it quickly shoved me off to really start thinking about query performance and optimization. Now I hardly ever use a pivot.

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 9

Sean Lange (1/27/2015)
Luis Cazares (1/27/2015)
Carlo Romagnano (1/27/2015)
Raghavendra Mudugal (1/27/2015)
Carlo Romagnano (1/27/2015)
Strange, but the version with PIVOT is a bit slower than that with GROUP BY. See execution plan!
...
actually I was doing the same thing on v2012, but did not see any difference in the EP, (not sure if I am missing something) but would like to know more... thank you.
See "Estimated Subtree Cost" and "Estimated Number of Rows"
Execution plan won't tell you which option is slower. Both values that you mention are just estimates that can be completely wrong. To know which one is faster, you need to measure time, not execution plans.
And certainly not the estimated execution plan. It is nearly worthless.

I think that both the actual and the estimated execution plan are very valuable. (And remember that they are far more similar than many people assume).

You just have to know which parts to read, and how to interpret them.

The percentages represented in execution plans can be extremely misleading. But that does not mean they are worthless, you just have to be aware what they represent.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

psingla Hall of Fame Points: 3840 More actions · Answer 10

Nice Question!

Pramod
SQL Server DBA | MCSE SQL Server 2012/2014
in.linkedin.com/in/pramodsingla/
http://pramodsingla.wordpress.com/

Andre Ranieri SSCrazy Points: 2819 More actions · Answer 11

+2 Thanks for the great question. I use PIVOT for some specific validation and testing scenarios, but it's not an every day thing. Thanks for the remind.

Arno Kwetters Hall of Fame Points: 3727 More actions · Answer 12

Pivot is always hard to iunderstand, but nice to work with.