Reputation: 4246
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
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