CODEWITHSUNDEEP
sqlsql-servercountpivotsql-null
Nicola Cossu
Nicola Cossu

Reputation: 56357

MSSQL How to handle null values to count them within pivot

I'm here again because I've another problem with pivot table that concerns with null values.

IF OBJECT_ID(N'tempdb..#exams') IS NOT NULL
BEGIN
DROP TABLE #exams
END
GO


create table #exams (
id uniqueidentifier,
exam nvarchar(max),
technician nvarchar(max)
)



insert into #exams 
values 
(newid(),'Esame1','Tecnico1'),
(newid(),'Esame2','Tecnico1'),
(newid(),'Esame1','Tecnico2'),
(newid(),'Esame3','Tecnico1'),
(newid(),'Esame3','Tecnico2'),
(newid(),'Esame3','Tecnico3'),
(newid(),'Esame3','Tecnico1'),
(newid(),'Esame1',NULL)

I have to handle in some way null values in my reports.

With sum case clause I could do simply in this way:

 select
 exam,
 sum(case when technician = 'Tecnico1' then 1 else 0 end) as Tecnico1,
 sum(case when technician = 'Tecnico2' then 1 else 0 end) as Tecnico2,
 sum(case when technician = 'Tecnico3' then 1 else 0 end) as Tecnico3,
 sum(case when technician is null then 1 else 0 end) as Unknown 
 from #exams
 group by exam
 order by exam
exam Tecnico1 Tecnico2 Tecnico3 Unkwnon
Esame1 1 1 0 1
Esame2 1 0 0 0
Esame3 2 1 1 0

but using pivot table (thanks again to Tole1010) null values stay outside from my pivot

select * from (
    select id,exam,
           technician 
           from #exams
    ) as t
    pivot 
    (   count(id)
            for technician in (Tecnico1,Tecnico2,Tecnico3)
        ) as t

and I get only:

exam Tecnico1 Tecnico2 Tecnico3
Esame1 1 1 0
Esame2 1 0 0
Esame3 2 1 1

Is there a way to add a column to count those null values using pivot syntax?

Upvotes: 0

Views: 373

Answers (1)

GMB
GMB

Reputation: 222422

You would typically replace null with something else, that does not otherwise appear in the column:

select * from (
    select id, exam,
       coalesce(technician , 'Unknown') as technician
    from #exams
) as t
pivot (   
    count(id)
    for technician in (Tecnico1,Tecnico2,Tecnico3, Unknown)
) as t

Demo on DB Fiddlde:

exam Tecnico1 Tecnico2 Tecnico3 Unknown
Esame1 1 1 0 1
Esame2 1 0 0 0
Esame3 2 1 1 0

Upvotes: 1

Related Questions