sql-server - 如何在数据透视 SQL 查询中用零替换 null

Question

我正在尝试获取年度和月份明智的销售，例如

 select * from
 (
 select year(InvDt) as [Year], left(datename(Month,InvDt), 3) as [Month], 
 InvAmnt as Amount
 from tblInvoice where TenantId =-xxxxxxxx
 ) as Inv
 pivot
 (sum(Amount) for [Month] in(Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, 
  Oct, Nov, [Dec])) as pvt

但是这里有这么多空值来了，所以我想用零替换这些空值。
请帮我。

Answer 1

create table tblInvoice (InvDt date, TenantId int, InvAmnt numeric)
insert into tblInvoice values ('20180601',1,1),('20180601',1,1),('20180601',1,1),('20180501',1,1), ('20180401',1,1)

SELECT Year
     , COALESCE(Jan   ,0) Jan
     , COALESCE(Feb   ,0) Feb
     , COALESCE(Mar   ,0) Mar
     , COALESCE(Apr   ,0) Apr
     , COALESCE(May   ,0) May
     , COALESCE(Jun   ,0) Jun
     , COALESCE(Jul   ,0) Jul
     , COALESCE(Aug   ,0) Aug
     , COALESCE(Sep   ,0) Sep
     , COALESCE(Oct   ,0) Oct
     , COALESCE(Nov   ,0) Nov
     , COALESCE(Dec   ,0) Dec
  FROM (   SELECT YEAR(InvDt)                     AS Year
                , LEFT(DATENAME(MONTH, InvDt), 3) AS Month
                , InvAmnt                         AS Amount
             FROM tblInvoice
            WHERE TenantId = 1) AS Inv
  PIVOT (   SUM(Amount)
            FOR Month IN (Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec)) AS pvt;