title: How To Set NULL is 0 in SQL Dynamic Pivot Query
body: <p>I developed one dynamic pivot table, and I set  <code>ISNULL = 0.00</code>.
  The query is given below </p>
  
  <pre><code>SELECT DISTINCT hdr.EmpNo,hdr.FirstName AS Name,
                  ISNULL(TBL.shortname,'')AS shortname, 
                  ISNULL(TBL.Amount,0.00) AS Amount,
                  TBL.Adtype INTO #Temp FROM  tbl1 AS hdr
  LEFT JOIN tbl2 TBL  ON TBL.EmpNo = hdr.EmpNo
  DECLARE @Ded AS NVARCHAR(MAX)
  SET @Ded = STUFF((SELECT ', ISNULL( ' + QUOTENAME(c.shortname) + ', 0.00 )AS ' + 
              QUOTENAME(c.shortname)  FROM (SELECT DISTINCT shortname FROM #Temp 
              WHERE ISNULL(shortname,'') &lt;&gt; '' AND Adtype = 2) AS c ORDER BY 
              shortname  FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') 
              ,1,1,'') 
  SET @Query = 'SELECT CAST(ROW_NUMBER() OVER(ORDER BY EmpNo ASC) AS  
                VARCHAR(100)) AS [S.No],EmpNo,Name,' + @Ded +' FROM  #Temp 
                PIVOT(SUM(Amount)  FOR shortname IN (' + @Ded + ')) AS PVTTable'
  EXEC sp_executesql @Query
  </code></pre>
  
  <p>But it displays the following error, and line 3 doesn't not have a "(".</p>
  
  <blockquote>
    <p>Msg 102, Level 15, State 1, Line 3
                       Incorrect syntax near '('.</p>
  </blockquote>
  
  <p>Any help to solve this problem would be appreciated. </p>