Instead of NULL how do I show `0` in result of a pivot table in SQL SERVER

Question

In my @disttable for some columns result is showing as null. Instead, I want to show 0. I want to show 'O' in Pivot table in place of NULL. I tried to give isnull for Total..But it did not work properly.

declare @DistTable Table
(   
  Party nvarchar(200),
  DistName nvarchar(200),
  Total int,
  TotalSeats int,
  DeclaredSeats int
)

insert into @DistTable

SELECT C.English AS Party,f.English as DistNAME,count(C.English) as Total,
   TOTALSEATS=(SELECT COUNT(*) FROM TBL_CONSTITUENCYMASTER c WHERE c.Phase= 3 and c.StateCode=29  and c.reg_code = 61),
   DECLAREDSEATs=(SELECT COUNT(*) FROM TBL_CONSTITUENCYMASTER c WHERE c.Phase= 3 and c.StateCode=29  and c.reg_code = 61 and Lead_WonCode=100)
FROM TBL_CONSTITUENCYMASTER A 
LEFT OUTER JOIN tbl_CandidateMaster B ON A.Lead_CandiCode = B.Cand_Code
LEFT OUTER JOIN tbl_AllianceMaster C ON B.AllianceCode = C.AllianceCode 
join tbl_regionmaster f on a.reg_code=f.reg_code
join tbl_olddistrictMaster e on a.Old_dist_code=e.old_dist_code
join tbl_DistrictMaster D on A.Dist_Code=D.Dist_Code WHERE A.STATECODE = 29 and A.Phase = 3 and A.Lead_WonCode = 100 and f.reg_code = 61 group by c.English,f.English order by F.English


select * from @DistTable pivot (min(Total) for Party in([TRS],[INC],[TDP],[BJP],[CPI],[CPM],[OTH])) as t

The result is

ADILABAD                163 163 67  27  NULL    NULL    NULL    NULL    69

Instead of NULL how do I show 0 in result of a pivot table in SQL SERVER?

[ISNULL (Transact-SQL)](https://learn.microsoft.com/en-us/sql/t-sql/functions/isnull-transact-sql?view=sql-server-2017), [COALESCE (Transact-SQL)](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/coalesce-transact-sql?view=sql-server-2017), [CASE (Transact-SQL)](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/case-transact-sql?view=sql-server-2017). Take your pick. A quick Google would have led you to at least one of these functions. — Thom A, Jan 28 '19 at 14:43
Might want a quick peek here also. ;) https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-table-aliases-like-a-b-c-or-t1-t2-t3 — Sean Lange, Jan 28 '19 at 14:51

Yogesh Sharma · Answer 1 · 2019-01-28T15:13:13.770

0

I would do conditional aggregation instead :

SELECT dt.DistName,
       min(case when dt.Party = 'TRS' then dt.Total else 0 end) as TRS,
       . . . 
FROM @DistTable dt
GROUP BY dt.DistName;

edited Jan 28 '19 at 15:13

answered Jan 28 '19 at 14:50

Yogesh Sharma

49,870
5
26
52

1

*"`COUNT(*)` will return `null` if corresponding rows are not found, while `COUNT(COL)` will return `0`."* Are you *sure*? [db<>fiddle](https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=059bb6dc5526fb929a2082e0b48060b9). – Thom A Jan 28 '19 at 15:02
@Larnu. . . That was out of mind, i was underimpression something like `COUNT(*)` or `COUNT(COL)` will return total rows (including `null`) or return total rows excluding `null`, So, i need some cup of tea, have you need ? I think no. – Yogesh Sharma Jan 28 '19 at 15:12
Oh, do you mean that `COUNT(*)` will include every row, even if every column has a value of `NULL`, where as `COUNT({Column})` will only count rows that don't have a value of `NULL` (for that column)? If so, yes you're correct. – Thom A Jan 28 '19 at 15:14

Instead of NULL how do I show `0` in result of a pivot table in SQL SERVER

1 Answers1