Insert Into #Happen Select v2.FItemID,t2.FItemID,Isnull(v2.FDCSPID,0),v2.FBatchNo,v2.FAuxPropID,0,0,
0,0,0,
Sum(IsNull(v2.FQty,0)),
Case When t1.FTrack<>81 Then Max(IsNull(v2.FPrice,0))
When t1.FTrack=81 Then Max(IsNull(v2.FPlanPrice,0)) Else 0 End,
Case When t1.FTrack<>81 Then Sum(IsNull(Round(v2.FAmount,2),0))
When t1.FTrack=81 Then Sum(IsNull(Round(v2.FPlanAmount,2),0)) Else 0 End,
0,Sum(IsNull(v2.FSecQty,0)),0
From ICStockBill v1
Inner Join ICStockBillEntry v2 On v1.FInterID=v2.FInterID
Left Join t_ICItem t1 On v2.FItemID=t1.FItemID
Left Join t_Stock t2 On v2.FDCStockID=t2.FItemID
Left Join t_StockPlace t11 On v2.FDCSPID=t11.FSPID
Where (v1.FTranType In (21,28,29,43) Or (V1.FTranType=100 And V1.FBillTypeID=12541)) And v1.FDate >=@begdate
And v1.FDate <@enddate
And t1.FNumber>=@beginvnumber And t1. FNumber<=@endinvnumber And t2.FNumber>=@begstocknumber AND t2.FNumber<=@endstocknumber
AND (SELECT t3.fnumber FROM t_item t3 WHERE t3.fitemid=t2.f_107)>=@begstockclassno
AND (SELECT t3.fnumber FROM t_item t3 WHERE t3.fitemid=t2.f_107)<=@endstockclassno
AND v1.FStatus>0 And v1.FCancelLation=0
Group By v2.FItemID,t2.FItemID,v2.FDCSPID,v2.FBatchNo,v2.FAuxPropID,v1.FTranType,t1.FTrackInsert Into #Happen Select v2.FItemID,t2.FItemID,Case When v1.FTranType=41 Then v2.FSCSPID Else v2.FDCSPID End,v2.FBatchNo,v2.FAuxPropID,
0,0,0,0,0,
Sum(IsNull(v2.FQty,0)),
Case When t1.FTrack<>81 Then Max(IsNull(v2.FPrice,0)) Else Max(IsNull(v2.FPlanPrice,0)) End,
Case When t1.FTrack<>81 Then Sum(IsNull(Round(v2.FAmount,2),0)) Else Sum(IsNull(Round(v2.FPlanAmount,2),0)) End,
0,0,Sum(IsNull(v2.FSecQty,0))
From ICStockBill v1
Inner Join ICStockBillEntry v2 On v1.FInterID=v2.FInterID
Left Join t_ICItem t1 On v2.FItemID=t1.FItemID
Left Join t_Stock t2 On v2.FSCStockID=t2.FItemID
Left Join t_MeasureUnit t3 On t1.FStoreUnitID=t3.FMeasureUnitID
Left Join t_StockPlace t11 On (Case When v1.FTranType=41 Then v2.FSCSPID Else v2.FDCSPID End)=t11.FSPID Where v1.FTranType In (24,41)
And v1.FDate >=@begdate
And v1.FDate <@enddate
And t1.FNumber>=@beginvnumber And t1. FNumber<=@endinvnumber And t2.FNumber>=@begstocknumber AND t2.FNumber<=@endstocknumber
AND (SELECT t3.fnumber FROM t_item t3 WHERE t3.fitemid=t2.f_107)>=@begstockclassno
AND (SELECT t3.fnumber FROM t_item t3 WHERE t3.fitemid=t2.f_107)<=@endstockclassno
AND v1.FStatus>0 And v1.FCancelLation=0 Group By v2.FItemID,t2.FItemID,Case When v1.FTranType=41 Then v2.FSCSPID Else v2.FDCSPID End,v2.FBatchNo,v2.FAuxPropID,v1.FTranType,t1.FTrack
Select v1.FItemID,v1.FStockID,v1.FStockPlaceID,v1.FBatchNo,v1.FAuxPropID,
Sum(v1.FBegQty) As FBegQty,Sum(v1.FBegBal) As FBegBal,
Sum(v1.FInQty) As FInQty,Max(v1.FInPrice) As FInPrice,Sum(v1.FInAmount) As FInAmount,
Sum(v1.FOutQty) As FOutQty,Max(v1.FOutPrice) As FOutPrice,Sum(v1.FOutAmount) As FOutAmount,
Sum(v1.FInSecQty) As FInSecQty,Sum(v1.FOutSecQty) As FOutSecQty,Sum(v1.FBegSecQty) As FBegSecQty Into #Happen1 From #Happen v1
Where 1 = 1 Group By v1.FItemID,v1.FStockID,v1.FStockPlaceID,v1.FBatchNo,v1.FAuxPropID
SET NOCOUNT ON
CREATE TABLE #ItemLevel(
FNumber1 Varchar(355),
FName1 Varchar(355),
FNumber2 Varchar(355),
FName2 Varchar(355),
FNumber3 Varchar(355),
FName3 Varchar(355),
FItemID int,
FNumber Varchar(355)) INSERT INTO #ItemLevel SELECT
CASE WHEN CHARINDEX('.',FFullNumber)-1= -1 or FLevel<2 THEN NULL ELSE SUBSTRING(FNumber, 1,CHARINDEX('.',FFullNumber)-1) END,
'',
CASE WHEN CHARINDEX('.',FFullNumber,CHARINDEX('.',FFullNumber)+1)-1= -1 or FLevel<3 THEN NULL ELSE SUBSTRING(FNumber, 1,CHARINDEX('.',FFullNumber,CHARINDEX('.',FFullNumber)+1)-1) END,
'',
CASE WHEN CHARINDEX('.',FFullNumber,CHARINDEX('.',FFullNumber,CHARINDEX('.',FFullNumber)+1)+1)-1= -1 or FLevel<4 THEN NULL ELSE SUBSTRING(FNumber, 1,CHARINDEX('.',FFullNumber,CHARINDEX('.',FFullNumber,CHARINDEX('.',FFullNumber)+1)+1)-1) END,
'',
FItemID,FNumber FROM t_Item
WHERE FItemClassID=4
AND FDetail=1 AND FNumber>=@beginvnumber AND FNumber<=@endinvnumber And exists (Select FItemID From #Happen Where #Happen.FItemID=t_Item.FItemID)
UPDATE t0 SET t0.FName1='[' + t1.FNumber + ']'+ t1.FName,t0.FName2='[' + t2.FNumber + ']'+ t2.FName,t0.FName3='[' + t3.FNumber + ']'+ t3.FName
FROM #ItemLevel t0 left join t_Item t1 On t0.FNumber1=t1.FNumber AND t1.FItemClassID=4 AND t1.FDetail=0
left join t_Item t2 On t0.FNumber2=t2.FNumber AND t2.FItemClassID=4 AND t2.FDetail=0
left join t_Item t3 On t0.FNumber3=t3.FNumber AND t3.FItemClassID=4 AND t3.FDetail=0 Create Table #Data(
FStockID int null,
FStockName Varchar(355) Null,
FName1 Varchar(355) Null,
FName2 Varchar(355) Null,
FName3 Varchar(355) Null,
FNumber Varchar(355) null,
FShortNumber Varchar(355) null,
FName Varchar(355) null,
FModel Varchar(355) null,
FUnitName Varchar(355) null,
FQtyDecimal smallint null,
FPriceDecimal smallint null,
FBegQty Decimal(28,10),
FBegPrice Decimal(28,10),
FBegBal Decimal(28,10),
FInQty Decimal(28,10),
FInPrice Decimal(28,10),
FInAmount Decimal(28,10),
FOutQty Decimal(28,10),
FOutPrice Decimal(28,10),
FOutAmount Decimal(28,10),
FEndQty Decimal(28,10),
FEndPrice Decimal(28,10),
FEndAmount Decimal(28,10),
FSumSort smallint not null Default(0),
FID int IDENTITY,
FBegSecQty Decimal(28,10) Default(0),FInSecQty Decimal(28,10) Default(0),
FOutSecQty Decimal(28,10) Default(0),
FBalSecQty Decimal(28,10) Default(0))
0,0,0,
Sum(IsNull(v2.FQty,0)),
Case When t1.FTrack<>81 Then Max(IsNull(v2.FPrice,0))
When t1.FTrack=81 Then Max(IsNull(v2.FPlanPrice,0)) Else 0 End,
Case When t1.FTrack<>81 Then Sum(IsNull(Round(v2.FAmount,2),0))
When t1.FTrack=81 Then Sum(IsNull(Round(v2.FPlanAmount,2),0)) Else 0 End,
0,Sum(IsNull(v2.FSecQty,0)),0
From ICStockBill v1
Inner Join ICStockBillEntry v2 On v1.FInterID=v2.FInterID
Left Join t_ICItem t1 On v2.FItemID=t1.FItemID
Left Join t_Stock t2 On v2.FDCStockID=t2.FItemID
Left Join t_StockPlace t11 On v2.FDCSPID=t11.FSPID
Where (v1.FTranType In (21,28,29,43) Or (V1.FTranType=100 And V1.FBillTypeID=12541)) And v1.FDate >=@begdate
And v1.FDate <@enddate
And t1.FNumber>=@beginvnumber And t1. FNumber<=@endinvnumber And t2.FNumber>=@begstocknumber AND t2.FNumber<=@endstocknumber
AND (SELECT t3.fnumber FROM t_item t3 WHERE t3.fitemid=t2.f_107)>=@begstockclassno
AND (SELECT t3.fnumber FROM t_item t3 WHERE t3.fitemid=t2.f_107)<=@endstockclassno
AND v1.FStatus>0 And v1.FCancelLation=0
Group By v2.FItemID,t2.FItemID,v2.FDCSPID,v2.FBatchNo,v2.FAuxPropID,v1.FTranType,t1.FTrackInsert Into #Happen Select v2.FItemID,t2.FItemID,Case When v1.FTranType=41 Then v2.FSCSPID Else v2.FDCSPID End,v2.FBatchNo,v2.FAuxPropID,
0,0,0,0,0,
Sum(IsNull(v2.FQty,0)),
Case When t1.FTrack<>81 Then Max(IsNull(v2.FPrice,0)) Else Max(IsNull(v2.FPlanPrice,0)) End,
Case When t1.FTrack<>81 Then Sum(IsNull(Round(v2.FAmount,2),0)) Else Sum(IsNull(Round(v2.FPlanAmount,2),0)) End,
0,0,Sum(IsNull(v2.FSecQty,0))
From ICStockBill v1
Inner Join ICStockBillEntry v2 On v1.FInterID=v2.FInterID
Left Join t_ICItem t1 On v2.FItemID=t1.FItemID
Left Join t_Stock t2 On v2.FSCStockID=t2.FItemID
Left Join t_MeasureUnit t3 On t1.FStoreUnitID=t3.FMeasureUnitID
Left Join t_StockPlace t11 On (Case When v1.FTranType=41 Then v2.FSCSPID Else v2.FDCSPID End)=t11.FSPID Where v1.FTranType In (24,41)
And v1.FDate >=@begdate
And v1.FDate <@enddate
And t1.FNumber>=@beginvnumber And t1. FNumber<=@endinvnumber And t2.FNumber>=@begstocknumber AND t2.FNumber<=@endstocknumber
AND (SELECT t3.fnumber FROM t_item t3 WHERE t3.fitemid=t2.f_107)>=@begstockclassno
AND (SELECT t3.fnumber FROM t_item t3 WHERE t3.fitemid=t2.f_107)<=@endstockclassno
AND v1.FStatus>0 And v1.FCancelLation=0 Group By v2.FItemID,t2.FItemID,Case When v1.FTranType=41 Then v2.FSCSPID Else v2.FDCSPID End,v2.FBatchNo,v2.FAuxPropID,v1.FTranType,t1.FTrack
Select v1.FItemID,v1.FStockID,v1.FStockPlaceID,v1.FBatchNo,v1.FAuxPropID,
Sum(v1.FBegQty) As FBegQty,Sum(v1.FBegBal) As FBegBal,
Sum(v1.FInQty) As FInQty,Max(v1.FInPrice) As FInPrice,Sum(v1.FInAmount) As FInAmount,
Sum(v1.FOutQty) As FOutQty,Max(v1.FOutPrice) As FOutPrice,Sum(v1.FOutAmount) As FOutAmount,
Sum(v1.FInSecQty) As FInSecQty,Sum(v1.FOutSecQty) As FOutSecQty,Sum(v1.FBegSecQty) As FBegSecQty Into #Happen1 From #Happen v1
Where 1 = 1 Group By v1.FItemID,v1.FStockID,v1.FStockPlaceID,v1.FBatchNo,v1.FAuxPropID
SET NOCOUNT ON
CREATE TABLE #ItemLevel(
FNumber1 Varchar(355),
FName1 Varchar(355),
FNumber2 Varchar(355),
FName2 Varchar(355),
FNumber3 Varchar(355),
FName3 Varchar(355),
FItemID int,
FNumber Varchar(355)) INSERT INTO #ItemLevel SELECT
CASE WHEN CHARINDEX('.',FFullNumber)-1= -1 or FLevel<2 THEN NULL ELSE SUBSTRING(FNumber, 1,CHARINDEX('.',FFullNumber)-1) END,
'',
CASE WHEN CHARINDEX('.',FFullNumber,CHARINDEX('.',FFullNumber)+1)-1= -1 or FLevel<3 THEN NULL ELSE SUBSTRING(FNumber, 1,CHARINDEX('.',FFullNumber,CHARINDEX('.',FFullNumber)+1)-1) END,
'',
CASE WHEN CHARINDEX('.',FFullNumber,CHARINDEX('.',FFullNumber,CHARINDEX('.',FFullNumber)+1)+1)-1= -1 or FLevel<4 THEN NULL ELSE SUBSTRING(FNumber, 1,CHARINDEX('.',FFullNumber,CHARINDEX('.',FFullNumber,CHARINDEX('.',FFullNumber)+1)+1)-1) END,
'',
FItemID,FNumber FROM t_Item
WHERE FItemClassID=4
AND FDetail=1 AND FNumber>=@beginvnumber AND FNumber<=@endinvnumber And exists (Select FItemID From #Happen Where #Happen.FItemID=t_Item.FItemID)
UPDATE t0 SET t0.FName1='[' + t1.FNumber + ']'+ t1.FName,t0.FName2='[' + t2.FNumber + ']'+ t2.FName,t0.FName3='[' + t3.FNumber + ']'+ t3.FName
FROM #ItemLevel t0 left join t_Item t1 On t0.FNumber1=t1.FNumber AND t1.FItemClassID=4 AND t1.FDetail=0
left join t_Item t2 On t0.FNumber2=t2.FNumber AND t2.FItemClassID=4 AND t2.FDetail=0
left join t_Item t3 On t0.FNumber3=t3.FNumber AND t3.FItemClassID=4 AND t3.FDetail=0 Create Table #Data(
FStockID int null,
FStockName Varchar(355) Null,
FName1 Varchar(355) Null,
FName2 Varchar(355) Null,
FName3 Varchar(355) Null,
FNumber Varchar(355) null,
FShortNumber Varchar(355) null,
FName Varchar(355) null,
FModel Varchar(355) null,
FUnitName Varchar(355) null,
FQtyDecimal smallint null,
FPriceDecimal smallint null,
FBegQty Decimal(28,10),
FBegPrice Decimal(28,10),
FBegBal Decimal(28,10),
FInQty Decimal(28,10),
FInPrice Decimal(28,10),
FInAmount Decimal(28,10),
FOutQty Decimal(28,10),
FOutPrice Decimal(28,10),
FOutAmount Decimal(28,10),
FEndQty Decimal(28,10),
FEndPrice Decimal(28,10),
FEndAmount Decimal(28,10),
FSumSort smallint not null Default(0),
FID int IDENTITY,
FBegSecQty Decimal(28,10) Default(0),FInSecQty Decimal(28,10) Default(0),
FOutSecQty Decimal(28,10) Default(0),
FBalSecQty Decimal(28,10) Default(0))
select FStockID,FName,FName1,
FName2,
FName3,
FNumber,'','','','',6,4,sum(FBegQty),case when sum(FBegQty) <> 0 then sum(FBegBal)/sum(FBegQty) else 0 end,sum(FBegBal),sum(FInQty),case when sum(FInQty) <> 0 then sum(FInAmount)/ sum(FInQty) else 0 end,sum(FInAmount),sum(FOutQty)
,case when sum(FOutQty) <> 0 then sum(FOutAmount)/sum(FOutQty) Else 0 end,sum(FOutAmount),sum(FEndQty),case when sum(FEndQty)<>0 then sum(FEndAmount)/sum(FEndQty) else 0 end,sum(FEndAmount),
0,Sum (FBegSecQty), Sum(FInSecQty), Sum(FOutSecQty), Sum(FBalSecQty) FROM
( Select t2.FItemID as FStockID,t2.FName as FName,tt1.FName1 as FName1,tt1.FName2 as FName2,tt1.FName3 as FName3,t1.FNumber as FNumber,'' as col1,'' as col2,'' as col3,'' as col4,6 as col5,4 as col6,
SUM(ISNULL(v2.FBegQty,0)) as FBegQty,Case When SUM(ISNULL(v2.FBegQty,0))<>0 then SUM(ISNULL(FBegBal,0))/SUM(cast(ISNULL(FBegQty,0) as Decimal(28,10))) Else 0 End as FBegPrice,
SUM(ISNULL(v2.FBegBal,0)) as FBegBal,SUM(ISNULL(FInQty,0)) as FInQty,Case When SUM(ISNULL(FInQty,0))<>0 Then SUM(ISNULL(FInAmount,0))/SUM(cast(FInQty as Decimal(28,10))) Else 0 End as FInPrice,
SUM(ISNULL(FInAmount,0)) as FInAmount,SUM(ISNULL(FOutQty,0)) as FOutQty, Case When SUM(ISNULL(FOutQty,0))<>0 Then SUM(ISNULL(FOutAmount,0))/SUM(cast(ISNULL(FOutQty,0) as Decimal(28,10))) Else 0 End as FOutPrice,
SUM(ISNULL(FOutAmount,0)) as FOutAmount,SUM(ISNULL(FBegQty,0))+SUM(ISNULL(FInQty,0))-SUM(ISNULL(FOutQty,0)) as FEndQty,
Case When SUM(ISNULL(FBegQty,0))+SUM(ISNULL(FInQty,0))-SUM(ISNULL(FOutQty,0))<>0 Then cast((SUM(ISNULL(FBegBal,0))+SUM(ISNULL(FInAmount,0))-SUM(ISNULL(FOutAmount,0))) as Decimal(28,10))/cast((SUM(ISNULL(FBegQty,0))+SUM(ISNULL(FInQty,0))-SUM(ISNULL(FOutQty,0))) as Decimal(28,10)) Else 0 End as FEndPrice,
Sum(ISNULL(FBegBal,0))+Sum(ISNULL(FInAmount,0))-Sum(ISNULL(FOutAmount,0)) as FEndAmount,0 as FSumSort,Sum(ISNULL(v2.FBegSecQty,0)) as FBegSecQty,Sum(ISNULL(v2.FInSecQty,0)) as FInSecQty,Sum(ISNULL(v2.FOutSecQty,0)) as FOutSecQty,Sum(ISNULL(v2.FBegSecQty,0))+Sum(ISNULL(v2.FInSecQty,0))-Sum(ISNULL(v2.FOutSecQty,0)) as FBalSecQty
From #Happen1 v2
Inner Join t_ICItem t1 On v2.FItemID=t1.FItemID
Left Join t_Stock t2 On v2.FStockID=t2.FItemID
Left Join t_AuxItem ta On v2.FAuxPropID=ta.FItemID
,#ItemLevel tt1
Where 1=1
AND t1.FItemID=tt1.FItemID Group By t2.FItemID,t2.FName,tt1.FName1,tt1.FName2,tt1.FName3,t1.FNumber
) t
Group by FStockID,FName,FName1,
FName2,
FName3,
FNumber
Update t1 Set t1.FName=t2.FName,t1.FShortNumber=t2.FShortNumber,t1.FModel=t2.FModel,
t1.FUnitName=t3.FName,t1.FQtyDecimal=t2.FQtyDecimal,t1.FPriceDecimal=t2.FPriceDecimal,
t1.FInPrice=(Case When t2.FTrack=81 Then t2.FPlanPrice Else t1.FInPrice End),
t1.FOutPrice=(Case When t2.FTrack=81 Then t2.FPlanPrice Else t1.FOutPrice End)
From #DATA t1 Left Join t_ICItem t2 On t1.FNumber = t2.FNumber
Left Join t_MeasureUnit t3 On t2.FUnitID=t3.FMeasureUnitID
Where t3.FStandard=1
SELECT td.*,tm.FName As FSecUnitName,
(SELECT t3.fname FROM t_item t3 WHERE t3.fitemid=t1.f_108) as region,
(SELECT t3.fname FROM t_item t3 WHERE t3.fitemid=t1.f_107) as stocklb
into #data1
FROM #Data td
Left Join t_ICItem t On t.FNumber=td.FNumber
LEFT JOIN t_stock t1 ON td.fstockid=t1.fitemid
Left Join t_MeasureUnit tm On t.FSecUnitID=tm.FMeasureUnitID
Where 1=1
AND FSumSort<100
ORDER by td.FID
SELECT t1.region,t1.stocklb,t1.fname1,t1.fname2,t1.fname3,
t1.fnumber,t1.fname,t1.fmodel,t1.FUnitName,sum(t1.FBegQty) AS FBEGQTY,
(case when sum(t1.FBegQty)=0 then 0 else sum(t1.fbegbal)/sum(t1.FBegQty) end )as FBegPrice,sum(t1.fbegbal) AS FBEGBAL,
sum(t1.finqty) AS FINQTY,
(case when sum(t1.finqty)=0 then 0 else sum(t1.finamount)/sum(t1.finqty) end ) as finprice,sum(t1.finamount) AS FINAMOUNT,
sum(t1.foutqty) AS FOUTQTY,
(case when sum(t1.foutqty)=0 then 0 else sum(t1.foutamount)/ sum(t1.foutqty) end ) as foutprice,sum(t1.foutamount) AS FOUTAMOUNT,
sum(t1.fendqty) AS FENDQTY,
( case when sum(t1.fendqty)=0 then 0 else sum(t1.fendamount)/ sum(t1.fendqty) end )as fendprice,sum(t1.fendamount) AS feNDAMOUNT,
0 as FSumSort
into #data2
FROM #data1 t1
GROUP by t1.region,t1.stocklb,t1.fname1,t1.fname2,t1.fname3,
t1.fnumber,t1.fname,t1.fmodel,t1.FUnitName
SELECT t1.region,t1.stocklb,t1.fname2,t1.fbegqty
into #data3
FROM #data2 t1
declare @sql varchar(8000),@sql1 varchar(8000)
select @sql1 = 'select case when grouping(stocklb)=1 then ''总计'' else stocklb end as 产品'
+ ',case when grouping(fname2)=1 then case when grouping(stocklb)=1 then ''''
else ''小计'' end else fname2 end as 日期'
select @sql = 'select stocklb,fname2'
select @sql1 = @sql1+','+'sum('+region+'期初) as ['+region+'期初]',
@sql = @sql+',sum(case when region = '''+region+''' then fbegqty else 0 end) as ['+region+'期初]'
from (select distinct region from #data3) a
select @sql = @sql + ' from #data3 group by stocklb,fname2'
select @sql1= @sql1+' from ('+@sql+') b group by stocklb,fname2 with rollup'
--print @sql1
exec(@sql1)
drop table #data3
Drop Table #Data
drop table #data1
drop table #data2
Drop Table #ItemLevel
Drop Table #Happen
Drop Table #Happen1在红色字体（动态SQL）前面#data3表示显示正常，有t1.region,t1.stocklb,t1.fname2,t1.fbegqty四个字段，我想把region由行转到列并分类汇总，所以用了动态SQL红色字体，但最终生成的表中只有stocklb和fname2两个字段，region没有转换，百思不得其解。
望各位专家指点，非常感谢。
#data3 在exec的会话中是不存在的，用全局的吧
帮帮忙啊
有。如果用select * from #data可以查询到