結果及陳述句如下，請問如何將結果中的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
uj5u.com熱心網友回復：
用ISNULL函式把需要查詢的列包一下
uj5u.com熱心網友回復：
結果是行轉列的，所以列是不固定的...單獨包某一列可行，但是列是動態的uj5u.com熱心網友回復：
結果是行轉列的，所以列是不固定的...單獨包某一列可行，但是列是動態的
uj5u.com熱心網友回復：
uj5u.com熱心網友回復：
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/12931.html
標籤：應用實例
上一篇：求救！MySQL安裝出了岔子！！