sql - sql中的垂直和水平分组依据

Question

请考虑下表(表名:销售)

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

我正在寻找一个查询，该查询按 Customer 对数据进行分组，并显示 Amount 的总和 Year 等于 2012 和 2013 (明确地)，以便它返回:

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

Answer 1

试试这个:

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

输出:

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