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]