sql查询两个数据透视列合并

xjreopfe 于 2021-07-26 发布在 Java

关注(0)|答案(1)|浏览(377)

select pvtMonth.CardName, [1] as [Jan Sales], [2] as [Feb Sales], [3] as [Mar Sales], [4] as [Apr Sales], [5] as [May Sales], 
[6] as [Jun Sales], [7] as [Jul Sales], [8] as [Aug Sales], [9] as [Sep Sales], [10] as [Oct Sales], [11] as [Nov Sales], [12] as [Dec Sales] from 
(
select X.CardName, SUM(X.[Total Sales S$]) as [Sales $] , X.Month from Data X group by X.CardName ,X.Month
) X PIVOT 
(
    sum(X.[Sales $])
    FOR [Month]
    IN ( [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12] )
) AS pvtMonth order by pvtMonth.CardName asc

现在我需要再添加一个名为“jan gp”的列名为“x.gp”。如何更新我的查询。我需要下面的结果集

sql window-functions sql-server pivot tsql

来源：https://stackoverflow.com/questions/62467647/sql-query-two-pivot-columns-combine

1条答案

按热度按时间

mlmc2os51#

我建议使用条件聚合而不是 pivot 语法。它更灵活（至少同样有效）。你似乎想要这样的东西：

select 
    cardName,
    sum(case when month = 1 then [Total Sales S$]  end) [Jan Sales $],
    sum(case when month = 1 then [Total Sales SGP] end) [Jan Sales GP],
    sum(case when month = 2 then [Total Sales S$]  end) [Feb Sales $],
    sum(case when month = 2 then [Total Sales SGP] end) [Feb Sales GP],
    ...
from data
group by cardName

赞(0）回复(0）举报 2021-07-26

我来回答

sql查询两个数据透视列合并

1条答案

相关问题

热门标签

最新问答