CODEWITHSUNDEEP
sql-serverpivot
FaisalThayyil
FaisalThayyil

Reputation: 85

SQL server pivot query not taking null count

I am running one Pivot query in SQLserver 2008 with below syntax to know the count of column name 'PrevalidationStatus' in table name tbInflowMaster. I am getting the answer but

count for null value is not coming .it show as 0 for null..I want Null value to be counted as Not processed in Not Processed field in the query..Please help me

I have written the below query it works for all the filed but not for null

SELECT BuyerName, [Completed] AS Completed, [WIP] 
AS
WIP, [Closed] AS Closed , ['NULL'] AS NotProcessd FROM (
    SELECT BuyerName,PrevalidationStatus FROM tbInflowMaster) ps

PIVOT(Count (PrevalidationStatus) FOR PrevalidationStatus IN 
    ( [Completed], [WIP], [Closed], ['NULL'])) 

AS pvt

I got outputas like this:

BuyerName          Completed    WIP Closed  NotProcessed
aniruddha.gupta  1      0   0   0
jintu.job           98      7   1   0
jashin      130     10  31  0
syed.sofi            76     6   44  0
seena.lijosh     260        31  0   0
deepak.khatua    55     6   3   0
tuhin.choudhury  144        0   1   0
shwetha.laxmi    133        5   5   0

My table 'tbInflowMaster' look like below(I have shown few data for example):

  BuyerName     PrevalidationStatus
seena.lijosh        Completed
tuhin.choudhury     Completed
tuhin.choudhury     NULL
tuhin.choudhury     Completed
jashin          Completed
tuhin.choudhury     Completed
tuhin.choudhury     Null

My table 'tbInflowMaster'

Upvotes: 2

Views: 1765

Answers (1)

Andrey Morozov
Andrey Morozov

Reputation: 7969

All you need is to replace NULL values with their "string representation".

Try this:

SELECT BuyerName, [Completed] AS Completed, [WIP] 
AS
WIP, [Closed] AS Closed , [NULL] AS NotProcessd FROM (
    SELECT BuyerName, isnull(PrevalidationStatus, 'NULL') [PrevalidationStatus] FROM tbInflowMaster) ps

PIVOT(Count (PrevalidationStatus) FOR PrevalidationStatus IN 
    ( [Completed], [WIP], [Closed], [NULL])) 

AS pvt

Check SQLFiddle

Upvotes: 2

Related Questions