How to Replace Nulls in PIVOT in SQL

问题:

I have the following code and I am trying to replace the Null that appear when using the pivot with zero. I do the following but it says that "Incorrect syntax near 'ISNULL'." I am not sure what I am doing wrong? Any suggestions please

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

The same the dyanmic SQL I am using. The above query is just shows the names so you can see what I am working with

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

Can you tell me what's wrong with this statement. i am having a syntax issue:

BEGIN 

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

    FETCH NEXT FROM CUR1 INTO @BranchName

END

回答1:

All PIVOT clause must be in bracket.

The result query must look like this:

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

How to generate parts of dynamic 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

回答2:

If you want to replace NULL from PIVOT TABLE with empty string then

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

This Converts the SUM value of UnitPrice to VARCHAR AND then replaces it with an empty string using ISNULL