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.