SQL:将 ISNULL 与动态数据透视一起使用 [英] SQL: Using ISNULL with dynamic pivot
问题描述
我想让枢轴产生的所有NULL值都变成0.我已将 ISNULL 放置在每个可以想象的地方，但似乎没有任何效果.枢轴是否与 ISNULL 兼容?代码如下:
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)
推荐答案
我会将您的查询设置为稍有不同，因为虽然列名在更改是动态的，但您仍然对列数进行了硬编码.
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.
首先，我将使用递归 CTE 来生成您要创建的月/年列表.
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
请参阅 SQL Fiddle with Demo.这将自动创建带有年份的 5 个月的列表.然后，您不会对 5 列进行硬编码.您当前的查询不够灵活.如果您想要 12 个月会发生什么，您将不得不更改您的代码.
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.
获取列列表的代码是:
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 将在最终的 null 值.
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)
请参阅 SQL Fiddle with Demo.此查询将为您提供结果:
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 |
这篇关于SQL:将 ISNULL 与动态数据透视一起使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
