The begin
DECLARE @ SQL VARCHAR (8000)
DECLARE @ COLUMN VARCHAR (8000)
SELECT @ SQL=ISNULL (@ SQL + ', ', ') + '[' + name +'] '
, @ COLUMN=ISNULL (@ COLUMN, ' ') + ', '+ name
NVL (t. "01", 0) "01"
The FROM
(SELECT 'beginning balance date, t4 F_105 name, CAST (t1) FAuxQty as a DECIMAL (13, 2)) number
The 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
The WHERE (t1) FDCStockID='27893' OR t1) FSCStockID='27893')
Company name and t1. FBatchNo like '% %' and t2. FDate & lt; 'the 2020-06-01 -/* */initial data
UNION all
SELECT the CONVERT (varchar (100), t2 FDate, 23) date, t4. F_105 name, CAST (t1) FAuxQty as a DECIMAL (13, 2)) number
The 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
The WHERE (t1) FDCStockID='27893' OR t1) FSCStockID='27893')
Company name and t1. FBatchNo like '% %' and t2. FDate between '2020-06-01' and '2020-07-31 s' the data -/* */
UNION all
SELECT 'ending balance date, t4 F_105 name, CAST ((SUM (case when T2. Then FTranType=97-1 * t1. FAuxQty else t1. FAuxQty end)) as a DECIMAL (13, 2)) number
The 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
The WHERE (t1) FDCStockID='27893' OR t1) FSCStockID='27893')
Company name and t1. FBatchNo like '% %' and t2. FDate & lt;
='2020-07-31'GROUP BY T4. F_105)
As cc
GROUP BY name
The ORDER BY name
The SET @ SQL='select * from
(SELECT "' beginning balance 'date, t4. F_105 name, CAST (t1) FAuxQty as a DECIMAL (13, 2)) number
The 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
The WHERE (t1 FDCStockID='27893' OR t1. FSCStockID='27893')
And t1. FBatchNo like '% %' and t2. The company name FDate & lt; "' 2020-06-01" '-/* */initial data
UNION all
SELECT the CONVERT (varchar (100), t2 FDate, 23) date, t4. F_105 name, CAST (t1) FAuxQty as a DECIMAL (13, 2)) number
The 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
The WHERE (t1 FDCStockID='27893' OR t1. FSCStockID='27893')
Company name and t1. FBatchNo like '% %' and t2. The FDate between ' ' ' '2020-06-01 and' 2020-07-31 s' the data -/* */
UNION all
SELECT "ending balance 'date, t4. F_105 name, CAST ((SUM (case when T2. Then FTranType=97-1 * t1. FAuxQty else t1. FAuxQty end)) as a DECIMAL (13, 2)) number
The 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
The WHERE (t1 FDCStockID='27893' OR t1. FSCStockID='27893')
And t1. FBatchNo like '% %' and t2. The company name FDate & lt;=' '2020-07-31' : '
GROUP BY T4. F_105)
As DDDD
Name of pivot (Max (quantity) for (+ SQL + '@')) in a '
The exec (@ SQL)
End
CodePudding user response:
List all packages need to query the using ISNULL function
CodePudding user response:
The result is turn line column, so listed is not fixed... Separate package a column is feasible, but the columns are dynamicCodePudding user response: