CODEWITHSUNDEEP
sql-servert-sqlpivot
user764754
user764754

Reputation: 4246

Pivot table with null column

Using sql server I want to create a pivot table that contains a null column. I tried:

SELECT account_no, SUM([500]) AS [500], SUM([NULL]) AS [the_null_col]
FROM (SELECT * FROM source_table) AS x
PIVOT (SUM(amount) FOR amount_debit IN ([500], [NULL])) AS y
GROUP BY account_no

but I only get: The incorrect value "NULL" is supplied in the PIVOT operator.

I want to use the pivot operator and no other way of creating a pivot table. I found this but the first answer isn't very detailed unfortunately.

Upvotes: 2

Views: 6050

Answers (1)

Martin Smith
Martin Smith

Reputation: 454020

The suggestion in the first answer in your link is to substitute a non null sentinel value in place of NULL and use that in the PIVOT.

SELECT account_no,
       [500],
       [-1] AS [Null]
FROM   (SELECT amount,
               ISNULL(amount_debit, -1) AS amount_debit,
               account_no
        FROM   source_table) AS x 
        PIVOT (SUM(amount) FOR amount_debit IN ([500], [-1])) AS y

This seems a bit of a hack and is vulnerable to breaking if you don't choose a value that can never appear in the data.

I'd do this without using the PIVOT keyword then it is trivial to check IS NULL. 

SELECT account_no,
       SUM(CASE
             WHEN amount_debit = 500 THEN amount
           END) AS [500],
       SUM(CASE
             WHEN amount_debit IS NULL THEN amount
           END) AS [NULL]
FROM   source_table
GROUP  BY account_no

Upvotes: 4

Related Questions