如何更换空值在SQL PIVOT(How to Replace Nulls in PIVOT in

我有以下的代码，我试图取代使用零枢轴时出现的空。 我这样做，但它说：“附近有语法错误ISNULL'。” 我不知道我做错了吗？ 任何建议，请

select *
from #tempfinaltable
pivot ISNULL(sum(TotalXSAAL),0) for Section_desc in
([Communication],[Construction],[Energy],[Financial Institutions],
 [General Property],[HIGHER ED & HEALTHCARE],
 [Inland Marine],[Real Estate])) AS AALs

同样我使用的是SQL dyanmic。 上述查询只显示名字，所以你可以看到我有工作

 select *
from #tempfinaltable
pivot (sum(TotalXSAAL) for Section_desc in
' + '('+@BranchNames++')) AS AALs'

你能告诉我什么是错的这一发言。 我有一个语法问题：

BEGIN 

    Set @ISNullBranchNames = @ISNullBranchNames + 'ISNULL('+(@BranchNames+',0),' 
    Set @BranchNames = @BranchNames + '['+@BranchName+'],'

    FETCH NEXT FROM CUR1 INTO @BranchName

END

Answer 1:

所有的PIVOT子句必须在支架上。

结果查询必须是这样的：

SELECT 
      'TotalXSAAL' as Col,
      ISNULL([Communication], 0) AS [Communication],
      ISNULL([Construction], 0) AS [Construction],
      ...,
      ...,
      ... 
FROM #tempfinaltable
PIVOT
(
  SUM(TotalXSAAL) for
  Section_desc in
  (
    [Communication],[Construction],[Energy],[Financial Institutions],
    [General Property],[HIGHER ED & HEALTHCARE],
    [Inland Marine],[Real Estate]
  )
)AS AALs

SQL FIDDLE DEMO

UPDATE

如何生成动态SQL的部分。

DECLARE @ISNullBranchNames nvarchar(MAX) = N'' -- you mast add empty string first, otherwise you will get NULL inresult
DECLARE @BranchNames nvarchar(MAX) = N''
.....
BEGIN 

    Set @ISNullBranchNames =
             @ISNullBranchNames + 'ISNULL([' + @BranchName + '], 0) AS [' + @BranchName +'], '
    Set @BranchNames = @BranchNames + '['+@BranchName+'],'

    FETCH NEXT FROM CUR1 INTO @BranchName

END

Answer 2:

如果要替换NULL与空字符串从数据透视表，然后

ISNULL ( CAST (SUM(UnitPrice]) as NVARCHAR),'') 

这单价为VARCHAR的总和值转换，然后使用ISNULL一个空字符串替换它

文章来源: How to Replace Nulls in PIVOT in SQL