将pivot中的空值替换为0

8dtrkrch  于 2021-08-09  发布在  Java
关注(0)|答案(1)|浏览(972)

我尝试在pivot函数中转换输出为0(零)的(null)值,但没有成功。
下表是我尝试过的语法:

SELECT DISTINCT isnull([DayLoad],0) FROM #Temp1

表中的数据 #Temp1 :

zone  dayB    templt cid  DayLoad
other 10      other   1    2020-05-28
other 10      other   1    2020-05-29
other 10      other   1    2020-05-30
other 10      other   1    2020-05-31
other 4       other   1    2020-06-02
other 10      other   1    2020-06-02
other 10      other   1    2020-06-01

我的请求:

DECLARE @cols NVARCHAR (MAX)
SELECT @cols = COALESCE (@cols + ',[' + CONVERT(NVARCHAR, [DayLoad], 106) + ']', 
                                  '[' + CONVERT(NVARCHAR, [DayLoad], 106) + ']')
               FROM    (SELECT DISTINCT [DayLoad] FROM #Temp1) PV  
               ORDER BY [DayLoad]

DECLARE @query NVARCHAR(MAX)
SET @query = '           
              SELECT *
                      into #temptable
                      FROM 
             (
                 SELECT 

                ''''+[zone]+'' ''          + ''''+convert(varchar(50),[dayB])+''''+''+''           +'' ''+(case when [templt]=''Прочее'' then '''' else [templt] end)+''''    as [zone/dayB]
                ,[DayLoad]
                ,[cid]
                ,[dayB]
                ,[zone]
                FROM #Temp1
             ) x
             PIVOT 
             (
                 sum([cid])  
                 FOR [DayLoad] IN ('+ @cols + ')
            ) p 

select *
from #temptable            
order by [zone],[dayB]

drop table #temptable      
            '     
EXEC(@query)
DROP TABLE #Temp1
cclgggtu

cclgggtu1#

请参考下面的示例,当您有空字符串和空字符串时,正确的选择是使用case语句,

SELECT DISTINCT case when ([DayLoad] is null or [DayLoad] = '') then 0 else [DayLoad] end FROM #Temp1

相关问题