sql pivot动态行转列NULL如何转为0

Db86 2020-08-18 04:49:02

结果及语句如下，请问如何将结果中的NULL转为0

begin

DECLARE @sql VARCHAR(8000)
DECLARE @COLUMN VARCHAR(8000)

SELECT @sql= ISNULL(@sql+',','')+ '['+名称+']'
,@COLUMN= ISNULL(@COLUMN,'')+', '+名称
----nvl(t."01",0) "01"

FROM
(SELECT ' 期初余额' 日期,t4.F_105 名称,CAST(t1.FAuxQty as DECIMAL(13,2)) 数量
FROM ICSTJGBillEntry t1
INNER JOIN ICSTJGBill T2 ON T1.FInterID=T2.FInterID
INNER JOIN t_item t3 ON t1.FItemID=t3.FItemID
INNER JOIN t_ICItem T4 ON t1.FItemID=T4.FItemID
WHERE (t1.FDCStockID='27893' OR t1.FSCStockID='27893')
and t1.FBatchNo like '%公司名%' and t2.FDate < '2020-06-01' ----/*期初数据*/
UNION all
SELECT CONVERT(varchar(100), t2.FDate, 23) 日期,t4.F_105 名称,CAST(t1.FAuxQty as DECIMAL(13,2)) 数量
FROM ICSTJGBillEntry t1
INNER JOIN ICSTJGBill T2 ON T1.FInterID=T2.FInterID
INNER JOIN t_item t3 ON t1.FItemID=t3.FItemID
INNER JOIN t_ICItem T4 ON t1.FItemID=T4.FItemID
WHERE (t1.FDCStockID='27893' OR t1.FSCStockID='27893')
and t1.FBatchNo like '%公司名%' and t2.FDate between '2020-06-01' and '2020-07-31' --/*发生数据*/
UNION all
SELECT '期末余额' 日期,t4.F_105 名称, CAST((SUM(case when T2.FTranType=97 then -1*t1.FAuxQty else t1.FAuxQty end)) as DECIMAL(13,2)) 数量
FROM ICSTJGBillEntry t1
INNER JOIN ICSTJGBill T2 ON T1.FInterID=T2.FInterID
INNER JOIN t_item t3 ON t1.FItemID=t3.FItemID
INNER JOIN t_ICItem T4 ON t1.FItemID=T4.FItemID
WHERE (t1.FDCStockID='27893' OR t1.FSCStockID='27893')
and t1.FBatchNo like '%公司名%' and t2.FDate <= '2020-07-31'
GROUP BY T4.F_105)

as cc
GROUP BY 名称
ORDER BY 名称

SET @sql= 'select * from
(SELECT '' 期初余额'' 日期,t4.F_105 名称,CAST(t1.FAuxQty as DECIMAL(13,2)) 数量
FROM ICSTJGBillEntry t1
INNER JOIN ICSTJGBill T2 ON T1.FInterID=T2.FInterID
INNER JOIN t_item t3 ON t1.FItemID=t3.FItemID
INNER JOIN t_ICItem T4 ON t1.FItemID=T4.FItemID
WHERE (t1.FDCStockID=''27893'' OR t1.FSCStockID=''27893'')
and t1.FBatchNo like ''%公司名%'' and t2.FDate < ''2020-06-01'' ----/*期初数据*/
UNION all
SELECT CONVERT(varchar(100), t2.FDate, 23) 日期,t4.F_105 名称,CAST(t1.FAuxQty as DECIMAL(13,2)) 数量
FROM ICSTJGBillEntry t1
INNER JOIN ICSTJGBill T2 ON T1.FInterID=T2.FInterID
INNER JOIN t_item t3 ON t1.FItemID=t3.FItemID
INNER JOIN t_ICItem T4 ON t1.FItemID=T4.FItemID
WHERE (t1.FDCStockID=''27893'' OR t1.FSCStockID=''27893'')
and t1.FBatchNo like ''%公司名%'' and t2.FDate between ''2020-06-01'' and ''2020-07-31'' --/*发生数据*/
UNION all
SELECT ''期末余额'' 日期,t4.F_105 名称, CAST((SUM(case when T2.FTranType=97 then -1*t1.FAuxQty else t1.FAuxQty end)) as DECIMAL(13,2)) 数量
FROM ICSTJGBillEntry t1
INNER JOIN ICSTJGBill T2 ON T1.FInterID=T2.FInterID
INNER JOIN t_item t3 ON t1.FItemID=t3.FItemID
INNER JOIN t_ICItem T4 ON t1.FItemID=T4.FItemID
WHERE (t1.FDCStockID=''27893'' OR t1.FSCStockID=''27893'')
and t1.FBatchNo like ''%公司名%'' and t2.FDate <= ''2020-07-31''
GROUP BY T4.F_105)
as dddd
pivot (max(数量) for 名称 in ('+@sql+'))a'

exec (@sql)

end

...全文