title: Sql Server Pivot Rows Into Columns with NULL Values As Column wtih default name
body: <p>I want to Pivot Rows into dynamic Columns which is working fine but the issue is NULL values in rows does not transformed into columns with any default name and instead of NULL I want ZERO(0) in value.</p>
  
  <p>Please refer to this image for output :</p>
  
  <p><a href="https://i.stack.imgur.com/dZBJd.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/dZBJd.png" alt=":"></a></p>
  
  <p>Below is my sql query with table :</p>
  
  <pre><code>CREATE TABLE #CourseSales
   (Course VARCHAR(50) null,Year INT,Earning MONEY null)
   go
   --Populate Sample records
   INSERT INTO #CourseSales VALUES('.NET',2012,10000)
   INSERT INTO #CourseSales VALUES('.NET',2012,5000)
   INSERT INTO #CourseSales VALUES('Java',2012,20000)
   INSERT INTO #CourseSales VALUES('Java',2013,30000)
   INSERT INTO #CourseSales VALUES(null,2013,1200)
   INSERT INTO #CourseSales VALUES(null,2012,null)
  
   select * from #CourseSales
  
  DECLARE @DynamicPivotQuery AS NVARCHAR(MAX),
          @PivotColumnNames AS NVARCHAR(MAX),
          @PivotSelectColumnNames AS NVARCHAR(MAX)
  
  --Get distinct values of the PIVOT Column
  SELECT @PivotColumnNames= ISNULL(@PivotColumnNames + ',','')+ QUOTENAME(Course)
  FROM (SELECT DISTINCT Course FROM #CourseSales) AS Courses
  
  --Get distinct values of the PIVOT Column with isnull
  SELECT @PivotSelectColumnNames = ISNULL(@PivotSelectColumnNames + ',','') + 'ISNULL(' + QUOTENAME(Course) + ', 0) AS ' + QUOTENAME(Course)
  FROM (SELECT DISTINCT Course FROM #CourseSales) AS Courses
  
  --Prepare the PIVOT query using the dynamic
  SET @DynamicPivotQuery =N'SELECT Year, ' + @PivotSelectColumnNames + ' FROM #CourseSales PIVOT(SUM(Earning) FOR Course IN (' + @PivotColumnNames + ')) AS PVTTable'
  
  --Execute the Dynamic Pivot Query
  EXEC sp_executesql @DynamicPivotQuery
  
  drop table #CourseSales
  </code></pre>
  
  <p>Any help would be appreciated. Thanks.</p>