October 7, 2013 at 6:34 pm
I'm trying to get my head around PIVOT. I understand the syntax and have successfully written a PIVOT command, but can't really see how pivoting is a great benefit over getting the data out using SQL, then putting it through SSRS reporting to do the same thing. Is the only benefit of PIVOT that it removes the need to do the extra formatting step (usually performed by a SSRS tablix)? When is a case where PIVOT is an amazingly helpful tool?
E.g. Is this a prime use-case for PIVOT, or are there cases where it is more beneficial? Writing a report for a hypothetical retail store - sales amounts by department:
Using PIVOT:
Department | 2010 | 2011 | 2012 | 2013
Whiteware | 2323 | 34.23 | 2.33| 12.23
Electronics | 2423 | 14.43 | 2.53| 7.23
Alternative:
Department | Year | Amount
Whiteware |2010 | 2323
Electronics | 2010| 2423
Whiteware | 2011 | 34.23
Electronics | 2011| 14.43
Whiteware | 2012 | 2.33
Electronics | 2012| 2.53
Whiteware | 2013 | 12.23
Electronics | 2013| 7.23
Then putting the above into a tablix in SSRS.
October 8, 2013 at 1:33 am
What if you're not using SSRS?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply