sql - SQL Server PIVOT 按多列

Question

我已经搜索了多个枢轴，但仍然没有找到与我的相似的。我正在使用 SQL Server 2008。

我已经用数据透视创建了一个查询：

select * 
from 
    (select 
         branch, sum(balance) [balance], 
         year(docdate) [year], month(docdate) [month]
     from 
         tempprt 
     group by 
         branch, year(docdate), month(docdate)) as a
pivot 
    (sum(balance) 
     for month in ([1], [2])) as pvt1

我得到这个输出：

现在，我想将每年的第1列和第 2列相加，并将年份作为列：

感谢您的帮助。谢谢！

标签： sqlsql-serversql-server-2008pivotmultiple-columns

Answer 1

你可以试试下面的脚本 -

select branch,

SUM(CASE WHEN year(docdate) = 2019 THEN balance ELSE 0 END) [2019],
SUM(CASE WHEN year(docdate) = 2019 AND MONTH(docdate) = 1 THEN balance ELSE 0 END) [2019_1],
SUM(CASE WHEN year(docdate) = 2019 AND MONTH(docdate) = 2 THEN balance ELSE 0 END) [2019_2],

SUM(CASE WHEN year(docdate) = 2018 THEN balance ELSE 0 END) [2018],
SUM(CASE WHEN year(docdate) = 2018 AND MONTH(docdate) = 1 THEN balance ELSE 0 END) [2018_1],
SUM(CASE WHEN year(docdate) = 2018 AND MONTH(docdate) = 2 THEN balance ELSE 0 END) [2018_2]

from tempprt 
GROUP BY branch