我尝试在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
1条答案
按热度按时间cclgggtu1#
请参考下面的示例,当您有空字符串和空字符串时,正确的选择是使用case语句,