仍为SQL数据透视表中的列获取空值

Question

在这一点上被难住了。在我的下面的查询中，它输出的结果很好，除了一个月，其中一个物料组没有该月的发票。因此，它输出一个空白字段，而不是为我提供该行的总计。

SELECT
[%0] AS 'Year',
 P.[ItemGroup] as 'BusinessUnit'
, [1] as Jan
, [2] as Feb
, [3] as Mar
, [4] as Apr
, [5] as May
, [6] as Jun
, [7] as Jul
, [8] as Aug
, [9] as Sep
, [10] as Oct
, [11] as Nov
, [12] as Dec
, [1] + [2] + [3] + [4] + [5] + [6] + [7] + [8] + [9] + [10] + [11] + [12] AS 'Grand Total'
From
(select CASE WHEN (T4.[ItmsGrpNam] = 'ADC Parts') Then 'Spare Parts'
WHEN T4.[ItmsGrpNam] = 'Bronze PM' Then 'PM'
WHEN T4.[ItmsGrpNam] = 'Competitor Parts' Then 'Spare Parts'
WHEN T4.[ItmsGrpNam] = 'Comprehen CareFee' Then 'Comprehensive Care' 
WHEN T4.[ItmsGrpNam] = 'Contractor Ins' Then 'Service' 
WHEN T4.[ItmsGrpNam] = 'Contractor Labour' Then 'Service' 
WHEN T4.[ItmsGrpNam] = 'Contractor Parts' Then 'Spare Parts' 
WHEN T4.[ItmsGrpNam] = 'Contractor PM' Then 'PM' 
WHEN T4.[ItmsGrpNam] = 'Delivery & Installat' Then 'Service' 
WHEN T4.[ItmsGrpNam] = 'IPSO Parts' Then 'Spare Parts' 
WHEN T4.[ItmsGrpNam] = 'Items' Then 'Service' 
WHEN T4.[ItmsGrpNam] = 'Reconditioned Parts' Then 'Spare Parts' 
WHEN T4.[ItmsGrpNam] = 'Service' Then 'Service' 
WHEN T4.[ItmsGrpNam] = 'Other Travel' Then 'Service' 
WHEN T4.[ItmsGrpNam] = 'Bags' Then 'Trolleys' 
WHEN T4.[ItmsGrpNam] = 'Trol Delivery' Then 'Trolleys' 
WHEN T4.[ItmsGrpNam] = 'Trolley Parts' Then 'Trolleys' 
WHEN T4.[ItmsGrpNam] = 'TrolleyRwM' Then 'Trolleys' 
WHEN T4.[ItmsGrpNam] = 'Trolleys' Then 'Trolleys' 
WHEN T4.[ItmsGrpNam] LIKE '%%Comm%%' Then 'Commercial' 
WHEN T4.[ItmsGrpNam] = 'Vended Ancillary' Then 'Commercial' 
WHEN T4.[ItmsGrpNam] = 'Merchant Fee and CC' Then 'Commercial' 
WHEN T4.[ItmsGrpNam] = 'Laundry P&A' Then 'Commercial' 
WHEN T4.[ItmsGrpNam] = 'Garment ID Parts' Then 'Labelling' 
WHEN T4.[ItmsGrpNam] = 'Heat Sealer' Then 'Labelling' 
WHEN T4.[ItmsGrpNam] = 'Labelling Service' Then 'Labelling' 
WHEN T4.[ItmsGrpNam] = 'PYO' Then 'Labelling' 
WHEN T4.[ItmsGrpNam] = 'Thermal Printers' Then 'Labelling' 
WHEN T4.[ItmsGrpNam] = 'Freight Income' Then 'Labelling' 
WHEN T4.[ItmsGrpNam] = 'Postage &Handling' Then 'Labelling' 
WHEN T4.[ItmsGrpNam] LIKE '%%Ind%%' Then 'Industrial' 
WHEN T4.[ItmsGrpNam] = 'Ironers' Then 'Industrial' 
WHEN T4.[ItmsGrpNam] = 'Second Hand Equip' Then 'Industrial' 
WHEN T4.[ItmsGrpNam] = 'EnviroSaver' Then 'EnviroSaver' 
WHEN T4.[ItmsGrpNam] = 'Ozone Parts' Then 'EnviroSaver' 
WHEN T4.[ItmsGrpNam] = 'Ozone Manufacturing' Then 'EnviroSaver' 
WHEN T4.[ItmsGrpNam] LIKE '%%Rent%%' Then 'Rent' 
ELSE T4.ItmsGrpNam END AS ItemGroup,sum(ISNULL(T0.[LineTotal],0)) as T, month(T1.[DocDate]) as Month from INV1 T0  
INNER JOIN OINV T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T3 ON T0.ItemCode = T3.ItemCode INNER JOIN OITB T4 ON T3.ItmsGrpCod = T4.ItmsGrpCod
where   year(T1.[DocDate]) = [%0]
Group by T1.[CardCode],T4.[ItmsGrpNam],T1.[DocDate]


UNION ALL

select CASE WHEN (T4.[ItmsGrpNam] = 'ADC Parts') Then 'Spare Parts'
WHEN T4.[ItmsGrpNam] = 'Bronze PM' Then 'PM'
WHEN T4.[ItmsGrpNam] = 'Competitor Parts' Then 'Spare Parts'
WHEN T4.[ItmsGrpNam] = 'Comprehen CareFee' Then 'Comprehensive Care' 
WHEN T4.[ItmsGrpNam] = 'Contractor Ins' Then 'Service' 
WHEN T4.[ItmsGrpNam] = 'Contractor Labour' Then 'Service' 
WHEN T4.[ItmsGrpNam] = 'Contractor Parts' Then 'Spare Parts' 
WHEN T4.[ItmsGrpNam] = 'Contractor PM' Then 'PM' 
WHEN T4.[ItmsGrpNam] = 'Delivery & Installat' Then 'Service' 
WHEN T4.[ItmsGrpNam] = 'IPSO Parts' Then 'Spare Parts' 
WHEN T4.[ItmsGrpNam] = 'Items' Then 'Service' 
WHEN T4.[ItmsGrpNam] = 'Reconditioned Parts' Then 'Spare Parts' 
WHEN T4.[ItmsGrpNam] = 'Service' Then 'Service' 
WHEN T4.[ItmsGrpNam] = 'Other Travel' Then 'Service' 
WHEN T4.[ItmsGrpNam] = 'Bags' Then 'Trolleys' 
WHEN T4.[ItmsGrpNam] = 'Trol Delivery' Then 'Trolleys' 
WHEN T4.[ItmsGrpNam] = 'Trolley Parts' Then 'Trolleys' 
WHEN T4.[ItmsGrpNam] = 'TrolleyRwM' Then 'Trolleys' 
WHEN T4.[ItmsGrpNam] = 'Trolleys' Then 'Trolleys' 
WHEN T4.[ItmsGrpNam] LIKE '%%Comm%%' Then 'Commercial' 
WHEN T4.[ItmsGrpNam] = 'Vended Ancillary' Then 'Commercial' 
WHEN T4.[ItmsGrpNam] = 'Merchant Fee and CC' Then 'Commercial' 
WHEN T4.[ItmsGrpNam] = 'Laundry P&A' Then 'Commercial' 
WHEN T4.[ItmsGrpNam] = 'Garment ID Parts' Then 'Labelling' 
WHEN T4.[ItmsGrpNam] = 'Heat Sealer' Then 'Labelling' 
WHEN T4.[ItmsGrpNam] = 'Labelling Service' Then 'Labelling' 
WHEN T4.[ItmsGrpNam] = 'PYO' Then 'Labelling' 
WHEN T4.[ItmsGrpNam] = 'Thermal Printers' Then 'Labelling' 
WHEN T4.[ItmsGrpNam] = 'Freight Income' Then 'Labelling' 
WHEN T4.[ItmsGrpNam] = 'Postage &Handling' Then 'Labelling' 
WHEN T4.[ItmsGrpNam] LIKE '%%Ind%%' Then 'Industrial' 
WHEN T4.[ItmsGrpNam] = 'Ironers' Then 'Industrial' 
WHEN T4.[ItmsGrpNam] = 'Second Hand Equip' Then 'Industrial' 
WHEN T4.[ItmsGrpNam] = 'EnviroSaver' Then 'EnviroSaver' 
WHEN T4.[ItmsGrpNam] = 'Ozone Parts' Then 'EnviroSaver' 
WHEN T4.[ItmsGrpNam] = 'Ozone Manufacturing' Then 'EnviroSaver' 
WHEN T4.[ItmsGrpNam] LIKE '%%Rent%%' Then 'Rent' 
ELSE T4.ItmsGrpNam END AS ItemGroup,sum(ISNULL(-T0.[LineTotal],0)) as T, month(T1.[DocDate]) as Month from RIN1 T0  
INNER JOIN ORIN T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T3 ON T0.ItemCode = T3.ItemCode INNER JOIN OITB T4 ON T3.ItmsGrpCod = T4.ItmsGrpCod
where   year(T1.[DocDate]) = [%0]
Group by T1.[CardCode],T4.[ItmsGrpNam],T1.[DocDate]

)S

Pivot
(Sum(T) For Month IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]))P


ORDER BY [ItemGroup] ASC

如果我将select语句中的值更改为ISNULL(9,0)，那么它将为我输出该月的0.00，但在总计列中该行仍为空值，即使每隔一个月都有值。

一旦一个月没有任何发票，它就会拒绝将其余月份相加。理想情况下，如果当月没有发票，我将只得到0值，但仍有一个总计。以今年为例，如果一个项目组在1月份有15000个，2月份有4000个，那么我总共会得到19000个，因为我们还没有达到其他任何月份。

编辑:添加图像以显示当前结果，因为您可以看到Sep for Industrial为空，以及该行的总计(GT)为空。这应该是该月份的0值，并且仍然包含所有月份的总和。

​

​

Answer 1

value + NULL将始终返回NULL。所以你需要在这里使用ISNULL：

ISNULL([1],0) + ISNULL[2],0) + ISNULL[3],0) + ISNULL[4],0) + ISNULL[5],0) + ISNULL[6],0) + ISNULL[7],0) + ISNULL[8],0) + ISNULL[9],0) + ISNULL[10],0) + ISNULL[11],0) + ISNULL[12] AS [Grand Total]

如果您通过条件聚合使用自定义透视表，例如：

Jan = SUM(CASE WHEN Month = 1 THEN T END),
Feb = SUM(CASE WHEN Month = 2 THEN T END),

然后你可以做的更简单：SUM(T) AS [Grand Total]