SQL:将ISNULL与动态数据透视表一起使用 [英] SQL: Using ISNULL with dynamic pivot
问题描述
我想使数据透视表产生的所有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)
我将您的查询设置为稍有不同，因为尽管它是动态的，因为列名不断变化，但是您仍然对列数进行了硬编码. /p>
首先，我将使用递归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的值替换为零.
然后将所有内容放在一起，代码将是:(注意:我使用的是您的带演示的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 |
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.
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 |
这篇关于SQL:将ISNULL与动态数据透视表一起使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
