Arina Arina - 4 months ago 8
SQL Question

Set Value With NULL When Pivot SQL Selection Return 0 Rows

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

Answer

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 
Comments