如何在数据透视表中使用sql server将空值替换为0

Question

我试图在数据透视表中替换0.00而不是null。查询是如下

SELECT * 
FROM (
    SELECT [dbo].[CLOI_ClientOrderItems].[cl_id] 'Customer Id' 
     ,[dbo].[CL_Clients].[cl_name] 'Customer Name' 
     ,FORMAT(in_date_issued, 'MMMM') AS 'Month' 
     ,[dbo].[IN_Invoices].[in_total] AS 'Invoice_Total' 
    FROM [dbo].[CLOI_ClientOrderItems] 
    INNER JOIN [dbo].[CL_Clients] ON [dbo].[CLOI_ClientOrderItems].[cl_id] = [dbo].[CL_Clients].[cl_id] 
    INNER JOIN [dbo].[IN_Invoices] ON [dbo].[CLOI_ClientOrderItems].[MasterOrderId] = [dbo].[IN_Invoices].[MasterOrderId] 
    WHERE YEAR(in_date_issued) = @in_date_issued 
     AND cl_system = 'DPO' 
    ) AS s 
PIVOT(Sum(Invoice_Total) FOR [Month] IN (
      january 
      ,february 
      ,march 
      ,april 
      ,may 
      ,june 
      ,july 
      ,august 
      ,september 
      ,october 
      ,november 
      ,december 
      )) AS pvt 

Answer 1

只需使用ISNULL/COALESCE：

SELECT col_names, 
     ISNULL(january, 0) AS january, 
     COALESCE(february, 0) AS february, 
     ... 
FROM ...