sql-server-2008 - SQL : Using ISNULL with dynamic pivot

Question

我想让枢轴产生的所有NULL值都变成0。我在每个能想到的地方都放了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)

Answer 1

我会设置您的查询略有不同，因为虽然它是动态的，因为列名在变化，但您仍然对列数进行了硬编码。

首先，我会使用递归 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 Fiddle with Demo .这将自动创建包含年份的 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 Fiddle with Demo .你会看到有两个版本。第一个@cols获取将在 pivot 中使用的列列表.第二个@colNames将在最后使用 SELECT替换 null 的列表带零的值。

然后将它们放在一起，代码将是:(注意:我使用的是您的 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)

参见 SQL Fiddle with Demo .此查询将为您提供结果:

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