SQL Pivot 查询末尾的列总计
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20651106/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Column Total at the end of SQL Pivot query
提问by Saifee
I have generated the following SQL Server 2008 pivot, and it gives me desire result. I want to add total column at end of pivot, where I'm finding it difficult.
我生成了以下 SQL Server 2008 数据透视表，它给了我想要的结果。我想在数据透视的末尾添加总列，我发现这很困难。
Please find the SQL I'm using for pivot
请找到我用于数据透视的 SQL
Select * from (
Select Case when (podocstatus = 'CL') then 'Closed PO'
when (podocstatus = 'OP') then 'Open PO'
when (podocstatus = 'SC') then 'Short Closed PO'
end as POStatus,
YEAR(podate) as [Year], YEAR(podate) as [poyear] , LEFT (datename(Month,podate),3) as [pomonth]
From PO_order_hdr
Where podocstatus IN ('SC','CL','OP')
) as POnumber
PIVOT
(
Count(poyear)
FOR [pomonth] IN (Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec)
)as PVT
Please help.
请帮忙。
回答by Dan
The easiest solution would be to simply do something like this:
最简单的解决方案是简单地做这样的事情：
Select *,
Jan + Feb + Mar + Apr + May + Jun + Jul + Aug + Sep + Oct + Nov + Dec AS [Total]
from
...
An alternative solution for the general case, would be to use a subselect. Move your inner query into a CTE, to make things a bit easier to work with:
对于一般情况，另一种解决方案是使用子选择。将您的内部查询移动到 CTE 中，使事情更容易使用：
WITH POnumber (POStatus, [Year], [poyear], [pomonth]) AS
(
Select sase when (podocstatus = 'CL') then 'Closed PO'
when (podocstatus = 'OP') then 'Open PO'
when (podocstatus = 'SC') then 'Short Closed PO'
end as POStatus,
YEAR(podate) as [Year], YEAR(podate) as [poyear] , LEFT (datename(Month,podate),3) as [pomonth]
From PO_order_hdr
Where podocstatus IN ('SC','CL','OP')
)
select *,
-- Subselect that counts the total for the given status and year:
(select count([Year]) from POnumber T
where T.POStatus = PVT.POStatus and T.poyear = PVT.poyear) as [Total]
from POnumber
PIVOT
(
Count(poyear)
FOR [pomonth] IN (Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec)
)as PVT
