Behnam Behnam - 5 months ago 25
SQL Question

Vertical and Horizontal Group by in sql

please consider table below (TableName: Sale)

ID Customer Year Amount
1 A 2012 1000
2 B 2012 2000
3 A 2012 1500
4 C 2013 1000
5 B 2013 2000


i'm looking for query which return below result:

Customer Year2012 Year2013
A 2500 0
B 2000 2000
C 0 1000

Answer

try this:

SELECT Customer,ISNULL([2012],0)as [2012],ISNULL([2013],0)as [2013]
FROM (
    SELECT 
       Customer,[Year] as [year], Amount
    FROM Mytest
) as s
PIVOT(SUM(AMOUNT)
      FOR Year IN ([2012], [2013])) AS PVTTable

OUTPUT:

Customer    2012    2013
A          2500      0
B          2000     2000
C           0       1000
Comments