Set Value With NULL When Pivot SQL Selection Return 0 Rows

debugcn Published at Dev

Arina

How to set NULL value when you select data using pivot return 0 rows?

;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

Those code will result something like this:

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

When I uncomment Filter by DivisionCode (on the first code)

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

The result become like this

Transaction                January  February  March .... Dec

How can I show the result like this?

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

Arina

I solved my problem by using union to show 0 when there's no data to display.

;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

Collected from the Internet

Please contact [email protected] to delete if infringement.