当透视 SQL 选择返回 0 行时，使用 NULL 设置值

Question

使用数据透视返回 0 行选择数据时如何设置 NULL 值？

;WITH cte AS (
SELECT
        DATENAME(month, RPT.DateID) as Month,
        ISNULL(SUM(RPT.TransactionIn), 0) as ATransactionIn,
        ISNULL(SUM(RPT.TransactionOut), 0) as BTransactionOut,
        ISNULL(SUM(RPT.OutstandingTransaction), 0) as COutstandingTransaction
        FROM RPT_SummaryPOApproval RPT
        WHERE RPT.Deleted = 0 --AND RPT.DivisionCode = 'asd'
        GROUP BY DATENAME(month, RPT.DateID)
), pivoted
as
(     
SELECT *
FROM (
    SELECT [Month], [Transactions], [Values]
    FROM (
        SELECT *
        FROM cte
    ) as p 
    UNPIVOT (
        [Values] FOR [Transactions] IN (ATransactionIn, BTransactionOut, COutstandingTransaction )
    ) as unpvt
) as k 
PIVOT (
    MAX([Values]) FOR [Month] IN ([January],[February],[March],[April],[May],[June],[July],[August],[September],[October],[November],[December])
) as pvt
)
SELECT * FROM pivoted
ORDER BY [Transactions] ASC 

这些代码将产生如下结果：

Transaction                 January  February  March .... Dec
ATransactionIn              12       0         0          0
BTransactionOut             10       0         0          0
COutstandingTransaction     2        0         0          0

当我取消注释时 按分区代码过滤（在第一个代码上）

WHERE RPT.Deleted = 0 AND RPT.DivisionCode = 'asd'

结果变成这样

Transaction                January  February  March .... Dec

我怎样才能像这样显示结果？

Transaction                January  February  March .... Dec
ATransactionIn              0        0         0          0
BTransactionOut             0        0         0          0
COutstandingTransaction     0        0         0          0

Answer 1

您可以对透视使用条件聚合：

WITH cte AS (
    SELECT DATENAME(month, RPT.DateID) as Month,
        SUM(CASE WHEN RPT.DivisionCode = 'asd' THEN RPT.TransactionIn ELSE 0 END) as ATransactionIn,
        SUM(CASE WHEN RPT.DivisionCode = 'asd' THEN RPT.TransactionOut ELSE 0 END) as BTransactionOut,
        SUM(CASE WHEN RPT.DivisionCode = 'asd' THEN RPT.OutstandingTransaction ELSE 0 END) as COutstandingTransaction
    FROM RPT_SummaryPOApproval RPT
    WHERE RPT.Deleted = 0
    GROUP BY DATENAME(month, RPT.DateID)
   ), . . .

查询的其余部分应相同。

Answer 2

当没有要显示的数据时，我通过使用联合显示 0 解决了我的问题。

;WITH cte AS (
SELECT
        DATENAME(month, RPT.DateID) as Month,
        ISNULL(SUM(RPT.TransactionIn), 0) as ATransactionIn,
        ISNULL(SUM(RPT.TransactionOut), 0) as BTransactionOut,
        ISNULL(SUM(RPT.OutstandingTransaction), 0) as COutstandingTransaction
        FROM RPT_SummaryPOApproval RPT
        WHERE RPT.Deleted = 0 --AND RPT.DivisionCode = 'asd'
        GROUP BY DATENAME(month, RPT.DateID)
        UNION ALL
        SELECT '0', '0', '0', '0'
), pivoted
as
(     
SELECT *
FROM (
    SELECT [Month], [Transactions], [Values]
    FROM (
        SELECT *
        FROM cte
    ) as p 
    UNPIVOT (
        [Values] FOR [Transactions] IN (ATransactionIn, BTransactionOut, COutstandingTransaction )
    ) as unpvt
) as k 
PIVOT (
    MAX([Values]) FOR [Month] IN ([January],[February],[March],[April],[May],[June],[July],[August],[September],[October],[November],[December])
) as pvt
)
SELECT * FROM pivoted
ORDER BY [Transactions] ASC