时间戳

首页 > 解决方案 > 我如何在 SQL SERVER 中的数据透视表的结果中显示“0”而不是 NULL

sql-server - 我如何在 SQL SERVER 中的数据透视表的结果中显示“0”而不是 NULL

问题描述

在我的某些列的@disttable 中，结果显示为空。相反，我想显示 0。我想在数据透视表中显示 'O' 来代替 NULL。我试图为 Total 提供 isnull ..但它不能正常工作。

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

结果是

ADILABAD                163 163 67  27  NULL    NULL    NULL    NULL    69

我如何0在 SQL SERVER 中显示数据透视表的结果而不是 NULL？

标签： sql-server

解决方案

我会改为进行条件聚合：

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

推荐阅读