November 13, 2015 at 9:59 am
I need to Pivot this query so that I get Year (2013,2014,2015) as columns and (Jan,Feb, Mar, ect...) as the rows
SELECT lmeEmployeeID
, CONVERT(CHAR(4), ompOrderDate, 100) as Month
, CONVERT(CHAR(4), ompOrderDate, 120) as year
, sum( ompOrderSubtotalBase)as total
FROMm1_KF.dbo.SalesOrders Left Join
m1_KF.dbo.Organizations on SalesOrders.ompCustomerOrganizationID = Organizations.cmoOrganizationID left Join
m1_KF.dbo.Employees on lmeEmployeeID = cmoAccountManagerEmployeeID Left Join
m1_KF.dbo.OrganizationLocations on Organizations.cmoOrganizationID = OrganizationLocations.cmlOrganizationID and
SalesOrders.ompShipLocationID = OrganizationLocations.cmlLocationID
Where lmeEmployeeID='ID' and ompOrderDate > '01-01-2013' and ompClosed =-1
group by lmeEmployeeID
, CONVERT(CHAR(4), ompOrderDate, 100), CONVERT(CHAR(4), ompOrderDate, 120)
order by CONVERT(CHAR(4), ompOrderDate, 100), CONVERT(CHAR(4), ompOrderDate, 120)
November 13, 2015 at 10:30 am
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.
_______________________________________________________________
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/
November 13, 2015 at 12:38 pm
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.
November 13, 2015 at 12:56 pm
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.
_______________________________________________________________
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/
November 13, 2015 at 1:00 pm
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.
It's true that performance is usually the same when comparing cross tabs versus a single pivot. When multiple columns or aggregates are needed, the cross tabs will always perform better. I'd prove it with actual code, but I don't have much time to set a real scenario right now. I wonder if an article about this would be a good idea.
November 13, 2015 at 9:15 pm
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.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 16, 2015 at 8:18 am
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.
_______________________________________________________________
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/
November 16, 2015 at 9:24 am
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.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply