大型交叉表問題，分不夠再加

[ny_nicholas]

庫存表(QCTotal)結構如下:
cb varchar(11)
lh varchar(11)
qc int

數據如下:
cb lh qc
1029000 31000403200 98
1029000 31010049101 2400
1029000 31010063200 81
1039000 31000403200 10864
1039000 31010040101 1323
1039000 31010049101 49
1039000 31010051101 502
... ... ...

訂單表(SoiTotal)庫結構如下
cb varchar(11)
oid varchar(11)
rdate datetime
lh varchar(11)
qotatal int
ot varchar(2)

數據如下:
cb oid rdate lh qototal ot
1029000 SP4001238 2004/3/5 31000403200 2 SP
1029000 SP4001238 2004/3/5 31010049101 2 SP
1029000 SO4004010 2004/8/20 31010063200 480 SO
1029000 ST4000415 2004/9/21 31030048201 200 ST
1029000 SO4005893 2004/11/20 31010049101 1000 SO
1039000 SO4006126 2004/12/2 31030048201 200 SO
1039000 ST4000475 2004/12/2 31030048201 12000 ST
1039000 SO4006864 2004/12/30 31010051101 2000 SO
1039000 SO4006965 2004/12/31 31010049101 17 SO
... .... ... .... ... ...

要實現的表如下(按cb='1029000')
　　　　　lh SP4001238 SO4004010 ST4000415 .... 訂單總量　庫存量　差異
31000403200 2 480 0 .... 482 　 98 -384
31010049101 2　　　　　　0 0 .... 2 2400 2398
.... .... .... .... .... .... .... ....

各位大俠幫忙呀！！

[ny_nicholas]

up

[ny_nicholas]

up

[子陌红尘]

declare @s varchar(8000)
set @s = ''

select @s = @s + ',['+oid+'] = sum(case when b.oid = '''+oid+''' then b.qototal else 0 end)'
from SoiTotal group by oid

set @s = 'select a.lh'+@s+',
isnull(sum(b.qototal),0) as 訂單總量,
a.qc as 庫存量,
(a.qc - isnull(sum(b.qototal),0)) as 差異
from
QCTotal a inner join SoiTotal b on a.lh = b.lh group by a.lh,a.qc'

exec(@s)

[zjcxc]

create proc p_qry
as
declare @s nvarchar(4000)
set @s=''
select @s+N','+quotename(oid)+N'=sum(case b.oid when '
+quotename(oid,N'''')+N' then b.qotatal end)'
from(select distinct oid=rtrim(oid) from SoiTotal)a
exec(N'select a.lh'+@s+'
,訂單總量=isnull(sum(b.qotatal),0)
,庫存量=a.qc
,差異=a.qc-isnull(sum(b.qotatal),0)
from QCTotal a
left join SoiTotal b on a.lh=b.lh and a.cb=b.cb
group by a.lh')

[zjcxc]

create proc p_qry
as
declare @s nvarchar(4000)
set @s=''
select @s+N','+quotename(oid)+N'=sum(case b.oid when '
+quotename(oid,N'''')+N' then b.qotatal end)'
from(select distinct oid=rtrim(oid) from SoiTotal)a
exec(N'select a.lh'+@s+'
,訂單總量=isnull(sum(b.qotatal),0)
,庫存量=a.qc
,差異=a.qc-isnull(sum(b.qotatal),0)
from QCTotal a
left join SoiTotal b on a.lh=b.lh and a.cb=b.cb
group by a.lh,a.qc')

[ny_nicholas]

我的會報錯呀，數據量大了，@s nvarchar(4000)改為(8000)不會錯，但再大些就錯了，有什麼辦法解決

[ny_nicholas]

@s nvarchar(8000)還不夠用怎麼辦??

[ny_nicholas]

在線等，急!!!

[zjcxc]

1.你用的是谁的方法?
2.测试数据量少时,该方法是否正确,正确了再解决其他问题

[Softlee81307]

Create Table qcTotal( cb varchar(11), lh varchar(11), qc int)
insert into qcTotal
select '1029000', '31000403200',98 union all
select '1029000', '31010049101',2400 union all
select '1029000', '31010063200',81 union all
select '1039000', '31000403200',10864 union all
select '1039000', '31010040101',1323 union all
select '1039000', '31010049101',49 union all
select '1039000', '31010051101',502
------------------表訂單表--------------
Create Table SoiTotal(cb varchar(11),oid varchar(11),rdate datetime,lh varchar(11),qotatal int,ot varchar(2) )
Insert Into SoiTotal
Select '1029000', 'SP4001238' , '2004/3/5','31000403200', 2 , 'SP' union all
Select '1029000', 'SP4001238', '2004/3/5','31010049101', 2 , 'SP' union all
Select '1029000', 'SO4004010', '2004/8/20','31010063200', 480 , 'SO' union all
Select '1029000', 'ST4000415', '2004/9/21','31030048201', 200 , 'ST' union all
Select '1029000', 'SO4005893', '2004/11/20','31010049101', 1000, 'SO' union all
Select '1039000', 'SO4006126', '2004/12/2','31030048201', 200 , 'SO' union all
Select '1039000', 'ST4000475', '2004/12/2','31030048201', 12000, 'ST' union all
Select '1039000', 'SO4006864', '2004/12/30','31010051101', 2000, 'SO' union all
Select '1039000', 'SO4006965' , '2004/12/31','31010049101', 17, 'SO'
----------------建過程實現----------------------
Create Proc kkk
as
select b.*,a.qc into #c from qcTotal a inner join
soitotal b on a.cb=b.cb and a.lh=b.lh
where a.cb='1029000'
---------------結果放在臨時表--------------
Declare @s varchar(4000)
set @s=''
select @s=@s+','+oid+'=isnull((select sum(qotatal) from #c where cb=a.cb and lh=a.lh and oid='''+oid+'''),0) '
from #c group by oid
set @s='select lh'+@s+',total=sum(qotatal),qc=sum(qc),cy=sum(qc)-sum(qotatal) from #c a group by cb,lh'
exec(@s)
--------------------過程結束--------------------------
---exec kkk ----執行過程
31000403200 0 0 2 2 98 96
31010049101 0 1000 2 1002 4800 3798
31010063200 480 0 0 480 81 -399

[ny_nicholas]

用了你的方法呀！ zjcxc(邹建)

[ny_nicholas]

(影響 255 個資料列)

伺服器: 訊息 208，層級 16，狀態 1，行 1
無效的物件名稱 'QoTotal'。
預存程序: example.dbo.p_qry
傳回碼 = 0

[ny_nicholas]

ALTER proc p_qry
as
declare @s nvarchar(4000)
set @s=''
select @s+N','+quotename(oid)+N'=sum(case b.oid when '
+quotename(oid,N'''')+N' then b.qototal end)'
from(select distinct oid=rtrim(oid) from SoiTotal)a
exec(N'select a.lh'+@s+'
,訂單總量=isnull(sum(b.qototal),0)
,庫存量=a.qc
,差異=a.qc-isnull(sum(b.qototal),0)
from QoTotal a
left join SoiTotal b on a.lh=b.lh and a.cb=b.cb
group by a.lh,a.qc')

[zjcxc]

create proc p_qry
as
set nocount on
declare @sqlhead nvarchar(4000),@sqlend nvarchar(4000)
,@sql1 nvarchar(4000),@sql2 nvarchar(4000),@sql3 nvarchar(4000),@sql4 nvarchar(4000)
,@i int,@ic nvarchar(10)

--生成数据处理临时表
select id=identity(int,0,1),g=0
,a=cast(N','
+quotename(oid)+N'=sum(case b.oid when '
+quotename(oid,N'''')+N' then b.qotatal end)'
as nvarchar(4000))
into # from(select distinct name from [syscolumns]) a

--分组临时表
update a set @i=id/i,g=@i
from # a,(select i=3800/max(len(a)) from #)b
set @ic=@i

--生成数据处理语句
select @sqlhead=quotename(N'select a.lh',N'''')
,@sqlend=quotename(N'
,訂單總量=isnull(sum(b.qotatal),0)
,庫存量=a.qc
,差異=a.qc-isnull(sum(b.qotatal),0)
from QCTotal a
left join SoiTotal b on a.lh=b.lh and a.cb=b.cb
group by a.lh,a.qc',N'''')
,@sql1=N'',@sql2=N'',@sql3=N'',@sql4=N''
while @ic>=0
select @sql1=N',@'+@ic+N' nvarchar(4000)'+@sql1
,@sql2=N',@'+@ic+N'=N'''''+@sql2
,@sql3=N',@'+@ic
+N'=case g when '+@ic
+N' then @'+@ic+N'+a else @'+@ic
+N' end'+@sql3
,@sql4=N'+@'+@ic+@sql4
,@ic=@ic-1
select @sql1=stuff(@sql1,1,1,N'')
,@sql2=stuff(@sql2,1,1,N'')
,@sql3=stuff(@sql3,1,1,N'')
,@sql4=stuff(@sql4,1,1,N'')

--执行
exec(N'declare '+@sql1+N'
select '+@sql2+N'
select '+@sql3+N' from #
exec(N'+@sqlhead+N'+'+@sql4+N'+N'+@sqlend+N')')

--删除临时表
drop table #

[ny_nicholas]

更正後為：
ALTER proc p_qry
as
declare @s nvarchar(4000)
set @s=''
select @s=N','+quotename(oid)+N'=sum(case b.oid when '
+quotename(oid,N'''')+N' then b.qototal end)'
from(select distinct oid=rtrim(oid) from SoiTotal)a

exec(N'select a.lh'+@s+'
,訂單總量=isnull(sum(b.qototal),0)
,庫存量=a.qc
,差異=a.qc-isnull(sum(b.qototal),0)
from QcTotal a
left join SoiTotal b on a.lh=b.lh and a.cb=b.cb
group by a.lh,a.qc')


GO

但顯示的數據為下
lh SO5000679 訂單總量 庫存量 差異
31000403200 null 0 1 1
31000403201 10 25432 2000 -23432

為什麼只有SO5000679這一列呢????

[zjcxc]

select @s=N' ...
--->
select @s=@s+N' ...

[ny_nicholas]

修改成select @s=@s+N' ...執行後出現以下錯誤:

伺服器: 訊息 105，層級 15，狀態 1，行 1
遺漏字元字串 'S
,訂單總量=isnull(sum(b.qototal),0)
,庫存量=a.qc
,差異=a.qc-isnull(sum(b.qototal),0)
from QcTotal a
left join SoiTotal b on a.lh=b.lh and a.cb=b.cb
group by a.lh,a.qc' 前面的引號。
伺服器: 訊息 170，層級 15，狀態 1，行 1
行 1: 'S
,訂單總量=isnull(sum(b.qototal),0)
,庫存量=a.qc
,差異=a.qc-isnull(sum(b.qototal),0)
from QcTotal a
left join SoiTotal b on a.l' 附近的語法不正確。

[zjcxc]

Create Table qcTotal( cb varchar(11), lh varchar(11), qc int)
insert into qcTotal
select '1029000', '31000403200',98 union all
select '1029000', '31010049101',2400 union all
select '1029000', '31010063200',81 union all
select '1039000', '31000403200',10864 union all
select '1039000', '31010040101',1323 union all
select '1039000', '31010049101',49 union all
select '1039000', '31010051101',502

------------------表訂單表--------------
Create Table SoiTotal(cb varchar(11),oid varchar(11),rdate datetime,lh varchar(11),qotatal int,ot varchar(2) )
Insert Into SoiTotal
Select '1029000', 'SP4001238' , '2004/3/5','31000403200', 2 , 'SP' union all
Select '1029000', 'SP4001238', '2004/3/5','31010049101', 2 , 'SP' union all
Select '1029000', 'SO4004010', '2004/8/20','31010063200', 480 , 'SO' union all
Select '1029000', 'ST4000415', '2004/9/21','31030048201', 200 , 'ST' union all
Select '1029000', 'SO4005893', '2004/11/20','31010049101', 1000, 'SO' union all
Select '1039000', 'SO4006126', '2004/12/2','31030048201', 200 , 'SO' union all
Select '1039000', 'ST4000475', '2004/12/2','31030048201', 12000, 'ST' union all
Select '1039000', 'SO4006864', '2004/12/30','31010051101', 2000, 'SO' union all
Select '1039000', 'SO4006965' , '2004/12/31','31010049101', 17, 'SO'
go

create proc p_qry
as
set nocount on
declare @sqlhead nvarchar(4000),@sqlend nvarchar(4000)
,@sql1 nvarchar(4000),@sql2 nvarchar(4000),@sql3 nvarchar(4000),@sql4 nvarchar(4000)
,@i int,@ic nvarchar(10)

--生成数据处理临时表
select id=identity(int,0,1),g=0
,a=cast(N','
+quotename(oid)+N'=sum(case b.oid when '
+quotename(oid,N'''')+N' then b.qotatal end)'
as nvarchar(4000))
into # from(select distinct oid=rtrim(oid) from SoiTotal) a

--分组临时表
update a set @i=id/i,g=@i
from # a,(select i=3800/max(len(a)) from #)b
set @ic=@i

--生成数据处理语句
select @sqlhead=quotename(N'select a.lh',N'''')
,@sqlend=N'
,訂單總量=isnull(sum(b.qotatal),0)
,庫存量=a.qc
,差異=a.qc-isnull(sum(b.qotatal),0)
from QCTotal a
left join SoiTotal b on a.lh=b.lh and a.cb=b.cb
group by a.lh,a.qc'
,@sql1=N'',@sql2=N'',@sql3=N'',@sql4=N''

while @ic>=0
select @sql1=N',@'+@ic+N' nvarchar(4000)'+@sql1
,@sql2=N',@'+@ic+N'=N'''''+@sql2
,@sql3=N',@'+@ic
+N'=case g when '+@ic
+N' then @'+@ic+N'+a else @'+@ic
+N' end'+@sql3
,@sql4=N'+@'+@ic+@sql4
,@ic=@ic-1
select @sql1=stuff(@sql1,1,1,N'')
,@sql2=stuff(@sql2,1,1,N'')
,@sql3=stuff(@sql3,1,1,N'')
,@sql4=stuff(@sql4,1,1,N'')

--执行
exec(N'declare '+@sql1+N'
select '+@sql2+N'
select '+@sql3+N' from #
exec(N'+@sqlhead+N'+'+@sql4+N'+N'''+@sqlend+N''')')

--删除临时表
drop table #
go

exec p_qry
go

drop table qcTotal,SoiTotal
drop proc p_qry

[ny_nicholas]

解決了，結貼，另開二貼放分，請 zjcxc(邹建)進來接分
http://community.csdn.net/Expert/topic/3834/3834371.xml?temp=.30888
http://community.csdn.net/Expert/topic/3834/3834377.xml?temp=.1013605