SQL：动态透视使用ISNULL(SQL: Using ISNULL with dynamic pi

我要让所有由枢轴生产成为0的NULL值。我已经把ISNULL在每一个地方想象，但似乎并没有产生任何影响。是枢轴与ISNULL兼容？下面的代码：

    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)

我会设置查询了略有不同，因为虽然它是动态的，列名正在发生变化，你仍然硬编码的列数。

首先，我会用递归CTE产生月/年要创建的列表。

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

请参阅SQL拨弄演示。这将创建你的5个月，自动今年的榜单。那么你就不是硬编码的5列。您当前的查询不灵活，因为它可以。会发生什么，如果你再想12个月中，你将不得不改变你的代码。

一旦你生成日期列表，我将它插入到一个临时表，所以你可以用它来获取列。

代码即可获得列的列表：

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,'')

请参阅SQL拨弄演示。你会看到，有两个版本。第一个@cols获取将在使用的列的列表pivot 。第二@colNames将在决赛中使用SELECT列表更换null与零值。

然后你把它放在一起，代码将是：（注：我使用的从你的版本我的回答前面的问题）

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)

请参阅SQL拨弄演示。该查询会给你的结果：

| 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 |