时间戳

首页 > 解决方案 > 通过 SQL Pivot 将“NULL”值替换为“0”

sql - 通过 SQL Pivot 将“NULL”值替换为“0”

问题描述

再会！我被我的查询困住了。我想将我的 NULL 值替换为零。

我的语法：

SELECT * FROM (SELECT (B.FIRST_NAME + ' ' + B.MIDDLE_INITIAL + ' ' + B.SURNAME) As 'Name', DATENAME(MONTH, A.COVERAGE_DATE) As 'Month', SUM(ISNULL((A.AMOUNT),0)) As 'Amount' 
FROM Transactions A, PledgerProfile B 
WHERE A.PLEDGER_ID = B.PLEDGER_ID AND DATENAME(Year, A.COVERAGE_DATE) = '2021' 
GROUP BY (B.FIRST_NAME + ' ' + B.MIDDLE_INITIAL + ' ' + B.SURNAME), DATENAME(MONTH, A.COVERAGE_DATE)) AS MonthlySalesData 
PIVOT(SUM(AMOUNT) 
FOR Month IN ([January],[February],[March],[April],[May], [June],[July],[August],[September],[October],[November], [December])) AS MNamePivot

给朋友打电话。有人可以调查一下并检查我的语法吗？我很感激任何反馈。谢谢！

我的输出：

我的输出

标签： sqlsql-serverpivot

解决方案

SELECT *您可以通过指定列/值而不是顶部IsNull()

例子

SELECT Name
      ,[January]   = isnull([January],0)
      ,[February]  = isnull([February],0)
      ,[March]     = isnull([March],0)
      ,[April]     = isnull([April],0)
      ,[May]       = isnull([May],0)
      ,[June]      = isnull([June],0)
      ,[July]      = isnull([July],0)
      ,[August]    = isnull([August],0)
      ,[September] = isnull([September],0)
      ,[October]   = isnull([October],0)
      ,[November]  = isnull([November],0)
      ,[December]  = isnull([December],0)
      ....

推荐阅读