CODEWITHSUNDEEP
sql-servernullpivot
lnsmith
lnsmith

Reputation: 27

Show NULL value as pivoted column in SQL Server

I need to pivot a count of values where it matches "animal type" in my code sample below. Unfortunately I may be missing records in col2 ('a','b'), but I need to count the animal type where they are in 'a','b', or null. I need to pivot the NULL into another column with that count value. I am doing a sum across all pivoted columns at the end of my query, and those with null values in col2 will not match the total. 

declare @testData   table   (   col1    varchar(10)
                            ,   col2    varchar(10)
                            )

insert into @testData   
values ('dog','a')
insert into @testData   
values ('cat','b')
insert into @testData   
values ('rabbit',null)

select * from @testData

select  
    pvt.col1        as  [Animal Type]
,   pvt.a           as  [A]
,   pvt.b           as  [B]
,   total.records   as  [Total Count]
from    (
            select 
                col1
            ,   col2
            ,   count(*)    as  records
            from    @testData   
            group by
                col1
            ,   col2
        )   as  s
pivot
        (
            sum(records)
            for     col2 in ([a], [b])
        )   as  pvt
join    (
            select 
                col1    
            ,   count(*)    as  Records
            from    @testData
            group by
                col1
        )   as  total               on  pvt.col1 = total.col1

Upvotes: 1

Views: 2225

Answers (1)

Brian Pressler
Brian Pressler

Reputation: 6713

Modify col2 in your subquery with an isnull function as shown below:

select  
    pvt.col1        as  [Animal Type]
,   pvt.a           as  [A]
,   pvt.b           as  [B]
,   pvt.[NULL]      as  [NULL]
,   total.records   as  [Total Count]
from    (
            select 
                col1
            ,   ISNULL(col2,'NULL') col2
            ,   count(*)    as  records
            from    @testData   
            group by
                col1
            ,   col2
        )   as  s
pivot
        (
            sum(records)
            for     col2 in ([a], [b], [NULL])
        )   as  pvt
join    (
            select 
                col1    
            ,   count(*)    as  Records
            from    @testData
            group by
                col1
        )   as  total               on  pvt.col1 = total.col1

Upvotes: 2

Related Questions