SQL: Using ISNULL with dynamic pivot

Question

I want to make all the NULL values produced by the pivot to become 0s. I have placed ISNULL in every place imaginable, but does not seem to have any effect. Are pivots compatible with ISNULL? Code below:

    DECLARE @startDate datetime
    SET @startDate = '2013-01-01'

    DECLARE @sql varchar(MAX)
    SET @sql = 'SELECT 
    CLIENTNAME, PROJECTNAME, RESOURCE, [' +
      REPLACE(SUBSTRING(CONVERT(varchar, @startDate, 13), 4, 8), ' ', '') + '], [' +
      REPLACE(SUBSTRING(CONVERT(varchar, DATEADD(MONTH, 1, @startDate), 13), 4, 8), ' ', '') + '], [' +
      REPLACE(SUBSTRING(CONVERT(varchar, DATEADD(MONTH, 2, @startDate), 13), 4, 8), ' ', '') + '], [' +
      REPLACE(SUBSTRING(CONVERT(varchar, DATEADD(MONTH, 3, @startDate), 13), 4, 8), ' ', '') + '], [' +
      REPLACE(SUBSTRING(CONVERT(varchar, DATEADD(MONTH, 4, @startDate), 13), 4, 8), ' ', '') + '], [' +
      REPLACE(SUBSTRING(CONVERT(varchar, DATEADD(MONTH, 5, @startDate), 13), 4, 8), ' ', '') + ']
    FROM
      (
      SELECT
        CLIENTNAME, PROJECTNAME, RESOURCE, FORECASTTOTAL
      FROM viewprojscheduling_group
      ) AS SourceTable
    PIVOT
      (
      SUM(FORECASTTOTAL)
      FOR SCHEDULEDDATE IN (' +
              QUOTENAME(REPLACE(SUBSTRING(CONVERT(varchar, @startDate, 13), 4, 8), ' ', '')) + ', ' +
              QUOTENAME(REPLACE(SUBSTRING(CONVERT(varchar, DATEADD(MONTH, 1, @startDate), 13), 4, 8), ' ', '')) + ', ' +
              QUOTENAME(REPLACE(SUBSTRING(CONVERT(varchar, DATEADD(MONTH, 2, @startDate), 13), 4, 8), ' ', '')) + ', ' +
              QUOTENAME(REPLACE(SUBSTRING(CONVERT(varchar, DATEADD(MONTH, 3, @startDate), 13), 4, 8), ' ', '')) + ', ' +
              QUOTENAME(REPLACE(SUBSTRING(CONVERT(varchar, DATEADD(MONTH, 4, @startDate), 13), 4, 8), ' ', '')) + ', ' +
              QUOTENAME(REPLACE(SUBSTRING(CONVERT(varchar, DATEADD(MONTH, 5, @startDate), 13), 4, 8), ' ', '')) + ')
      ) AS PivotTable'

    execute(@sql)

Answer 1

I would set your query up slightly different because while it is dynamic in that the column names are changing, you have still hard-coded the number of columns.

First, I would use a recursive CTE to generate the list of months/years that you want to create.

DECLARE @startDate datetime

SET @startDate = '2013-01-01'

;with dates as
(
  select @startdate datelist, 1 sp
  union all
  select dateadd(month, 1, datelist), sp+1
  from dates
  where sp+1 <= 5 -- change this number 5 to the number of months you need
)
select   sp,
  REPLACE(SUBSTRING(CONVERT(varchar(11), datelist, 13), 4, 8), ' ', '') MONTHANDYEAR
from dates

See SQL Fiddle with Demo. This is going to create your list of the 5 months with the year automatically. Then you are not hard-coding the 5 columns. Your current query is not as flexible as it could be. What will happen if you then want 12 months, you are going to have to change your code.

Once you generate the list of dates, I would insert it into a temp table so you can use it to get the columns.

The code to get the list of columns is:

select @cols = STUFF((SELECT ',' + QUOTENAME(monthandyear) 
                    from #datesTemp
                    group by monthandyear, sp
                    order by sp
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

select @colNames = STUFF((SELECT  ', isnull(' + QUOTENAME(monthandyear)+', 0) as '+QUOTENAME(monthandyear)
                    from #datesTemp
                    group by monthandyear, sp
                    order by sp
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

See SQL Fiddle with Demo. You will see that there are two versions. The first one @cols gets the list of columns that will be used in the pivot. The second @colNames will be used in the final SELECT list to replace the null values with the zeros.

Then you put it all together and the code will be: (Note: I am using a version of my answer from your previous question)

DECLARE @cols AS NVARCHAR(MAX),
    @colNames AS NVARCHAR(MAX),
    @query AS NVARCHAR(MAX),
    @startDate datetime

SET @startDate = '2013-01-01'

;with dates as
(
  select @startdate datelist, 1 sp
  union all
  select dateadd(month, 1, datelist), sp+1
  from dates
  where sp+1 <= 5 -- change this number 5 to the number of months you need
)
select   sp,
  REPLACE(SUBSTRING(CONVERT(varchar(11), datelist, 13), 4, 8), ' ', '') MONTHANDYEAR
into #datesTemp
from dates

select @cols = STUFF((SELECT ',' + QUOTENAME(monthandyear) 
                    from #datesTemp
                    group by monthandyear, sp
                    order by sp
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

select @colNames = STUFF((SELECT  ', isnull(' + QUOTENAME(monthandyear)+', 0) as '+QUOTENAME(monthandyear)
                    from #datesTemp
                    group by monthandyear, sp
                    order by sp
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')


set @query = 'SELECT resource, clientname,' + @colNames + ' 
             from 
             (
                select [CLIENTNAME], [RESOURCE], [FORECASTTOTAL],
                   REPLACE(SUBSTRING(CONVERT(varchar(11), SCHEDULEDDATE, 13), 4, 8), '' '', '''') monthandyear
                from viewprojscheduling_group
            ) x
            pivot 
            (
                sum(FORECASTTOTAL)
                for monthandyear in (' + @cols + ')
            ) p '

execute(@query)

See SQL Fiddle with Demo. This query will give you the result:

| RESOURCE | CLIENTNAME | JAN2013 | FEB2013 | MAR2013 | APR2013 | MAY2013 |
---------------------------------------------------------------------------
|     res1 |        abc |    1000 |    2000 |       0 |       0 |       0 |
|     res1 |        def |       0 |       0 |    2000 |       0 |       0 |
|     res2 |        def |    1500 |       0 |       0 |       0 |       0 |
|     res3 |        ghi |       0 |       0 |    2500 |       0 |       0 |