如何从sql透视表中删除null值

Question

如何将sql数据透视表中的空值替换为零？

DECLARE @cols AS NVARCHAR(MAX),
@query  AS NVARCHAR(MAX);
SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.[Offer_cover]) 
FROM #cover2 c 
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)') 
,1,1,'')

set @query = 'SELECT Ref,role_name,offer_id, ' + @cols + ' from 
(
select*
from #cover2 
) x
pivot 
(
SUM(cover_earning_Count)
for [offer_cover] in (' + @cols + ') 
) p   '
execute(@query)

输出：

Ref         role_name    offer_id   10000   104000  112000
43132_43282 Call Center  1          1       NULL    NULL
43132_43282 Others       1          2       NULL    NULL
43160_43282 Call Center  1          6       NULL    1
43160_43282 Others       1          NULL    1       NULL
43191_43282 Call Center  1          7       NULL    NULL
43191_43282 Others       1          3       1       1
43221_43282 Call Center  1          4       1       1
43221_43282 Others       1          2       NULL    NULL

封面2表格

Ref YEAR    MONTH   Role_name   offer_cover offer_id    Cover_Earning_Count CONF_DATE   Curr_date
43132_43282 2018    2   Call Center 2000    1   5   2/1/2018    7/1/2018
43132_43282 2018    2   Call Center 4000    1   8   2/1/2018    7/1/2018
43132_43282 2018    2   Call Center 6000    1   2   2/1/2018    7/1/2018
43132_43282 2018    2   Call Center 8000    1   4   2/1/2018    7/1/2018
43132_43282 2018    2   Call Center 10000   1   1   2/1/2018    7/1/2018
43132_43282 2018    2   Call Center 12000   1   6   2/1/2018    7/1/2018
43132_43282 2018    2   Call Center 14000   1   2   2/1/2018    7/1/2018
43132_43282 2018    2   Call Center 16000   1   4   2/1/2018    7/1/2018
43132_43282 2018    2   Call Center 20000   1   6   2/1/2018    7/1/2018
43132_43282 2018    2   Call Center 24000   1   5   2/1/2018    7/1/2018

Answer 1

正如我之前在评论中提到的，您需要2@cols，一个用于select，另一个用于pivot

DECLARE @cols   AS NVARCHAR(MAX),   -- for pivot
@cols2  AS NVARCHAR(MAX),   -- for select
@query  AS NVARCHAR(MAX);
SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.[Offer_cover]) 
FROM #cover2 c 
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)') 
,1,1,'')
-- this is for the SELECT
SET @cols2 = STUFF((SELECT distinct ',' + 'ISNULL(' + QUOTENAME(c.[Offer_cover]) + ', 0) ' + QUOTENAME(c.[Offer_cover])
FROM #cover2 c 
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)') 
,1,1,'')
set @query = 'SELECT Ref,role_name,offer_id, ' + @cols2 + ' from 
(
select *
from #cover2 
) x
pivot 
(
SUM(cover_earning_Count)
for [Offer_cover] in (' + @cols + ') 
) p'
-- do a print to verify the query
print @query

Answer 2

尝试使用ISNULL

DECLARE @cols AS NVARCHAR(MAX),
@query  AS NVARCHAR(MAX);
SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.[Offer_cover]) 
FROM #Table1 c 
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)') 
,1,1,'')
set @query = 'SELECT Ref,role_name,offer_id, ' + isnull(@cols,0) + ' from 
(
select*
from #Table1 
) x
pivot 
(
SUM(cover_earning_Count)
for [offer_cover] in (' + @cols + ') 
) p   '
execute(@query)