Sean Lange (11/16/2015)
Jeff Moden (11/13/2015)
Sean Lange (11/13/2015)
Ed Wagner (11/13/2015)
Sean Lange (11/13/2015)
Instead of a PIVOT I would recommend using a crosstab. The syntax is far less obtuse to me and it even has a slight performance benefit over PIVOT. You can find two articles in my signature discussing this technique in detail. The first is when you know how many columns and the second is the dynamic version.If you need help we can help but will require some more details posted. Please take a few minutes to read the first link in my signature for best practices when posting questions.
I couldn't agree more with what Sean posted. The performance benefit it worth it alone, but the syntax makes sense, as opposed to the pivot, which I have to look up every single time. Jeff's articles on crosstabs will get you going in the right direction.
Jeff has argued that the performance enhancement is fairly negligible on more modern machines. Those articles are now 5 years old. Maybe we should see if he is willing to update his performance findings with more modern hardware so we can truly compare the differences.
I ran the million row test included in the script in the resources of that article, the times were roughly 1/10th of what I had posted at the bottom of that article but the relationships were basically identical. The simple Pivot was still beat by the CROSS TAB by a bit and preaggregated CROSS TABs still smoked preaggregated PIVOTs by nearly a factor of two.
Part of the reason why I post performance test code in most of my articles so that folks can see what the methods will do after MS makes changes or the code is executed on improved hardware. Run the code and see what you get.
I was not trying to come across as lazy so you could run the tests for me Jeff. I remembered a discussion where you sort of shook me off when I suggested the performance benefits of cross tab vs pivot on modern machines. My experience has been routinely that cross tabs blow them away but I have been careful not to suggest much performance benefit after you stating it doesn't make much difference. I always appreciate that you post all the details for everyone to run tests themselves and I have done this particular one several times on all sorts of various platforms and sql versions.
As always, "It Depends". Like you, I've found that CROSSTABs almost always beat the performance of PIVOTs especially when large amounts of data are used. And, yes, I agree that on modern machines the differences have become almost trivial unless you have the opportunity to do a pre-aggregation, in which case CROSSTABs blow the doors off of even pre-aggregated PIVOTs by a factor of 2:1.
To set the record straight, I can't see using two methods to do the same thing especially when one of those methods is always a little faster (no matter how trivial) and, in the face of pre-aggregation, is substantially faster. With that thought in mind, someone would have to point a loaded gun at my head to force me to implement a PIVOT rather than a CROSSTAB and I'd probably opt to try to take the gun away rather than deploy code with a PIVOT in it. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.