IBM Rational 系统开发最佳实践工具包	WebSphere MQ 最佳实践 TOP 15

列行转换字段排序的问题!(急!)

楼主gerrley（gerrley）2006-03-14 15:51:44 在 MS-SQL Server / 基础类提问

有如下两个表:
表tblsizeQty farea,fres,fcomkey,fsize ,ftype,fqty
表tblsizeelem fsize,fsequence
表tblsizeelem中的数据:
Fsize fsequence
S 1
M 2
L 3
XL 4
XXL 5
...
tblsizeQty中有如下数据:
farea fres fcomkey fsize ftype fqty
A Res1 1000 S Order1 100
A Res1 1000 M Order1 103
A Res1 1000 XL Order2 102
B Res1 1000 S Order1 100
B Res1 1000 M Order1 103
B Res1 1000 XXL Order2 102

现在需要把Fsize转为行得到如下结果:
farea fres fcomkey ftype S M XL XXL
A Res1 1000 Order1 100 103 0 0
A Res1 1000 Order2 0 0 102 0
B Res1 1000 Order1 100 103 0 0
B Res1 1000 Order2 0 0 0 102

用了如下语句
declare @sql varchar(2000)
set @sql=''
select @sql=@sql+',['+[fsize]+']=sum(case when [fsize]='''+[fsize]+''' then [fqty] else 0 end)'
from tblsizeQty
group by [fsize]
set @sql='select farea,fres,fcomkey,Ftype '+@sql+'from tblsizeQty group by [farea], [fres], [fcomkey], [ftype]'
exec(@sql)

这样结果是正确了,但是FSize排序是乱的,怎么与tblsizeelem关连,让Fsize列转为行是按照tblsizeelem.fsequence的顺序排出来?

问题点数：100、回复次数：11Top

1 楼lsqkeke（可可）回复于 2006-03-14 15:58:41 得分 0

try:
declare @sql varchar(2000)
set @sql=''
select @sql=@sql+',['+a.[fsize]+']=sum(case when [fsize]='''+a.[fsize]+''' then [fqty] else 0 end)'
from tblsizeQtyn a,tblsizeelem b
where a.Fsize=b.Fsize
group by [fsize]
order by b.fsequence

set @sql='select farea,fres,fcomkey,Ftype '+@sql+'from tblsizeQty group by [farea], [fres], [fcomkey], [ftype]'
exec(@sql)Top

2 楼happyflystone（无枪的狙击手）回复于 2006-03-14 15:59:44 得分 10

declare @sql varchar(2000)
set @sql=''
select @sql=@sql+',['+[fsize]+']=sum(case when [fsize]='''+[fsize]+''' then [fqty] else 0 end)'
from (select Fsize from @tblsizeelem ) a

set @sql='select farea,fres,fcomkey,Ftype '+@sql+'from tblsizeQty group by [farea], [fres], [fcomkey], [ftype]'
exec(@sql)Top

3 楼rivery（river）回复于 2006-03-14 15:59:44 得分 10

--考虑可能fsize与fsequence一一对应。可以是试试。
declare @sql varchar(2000)
set @sql=''
select @sql=@sql+',['+[fsize]+']=sum(case when [fsize]='''+[fsize]+''' then [fqty] else 0 end)'
from tblsizeelem
group by [fsize],fsequence
order by fsequenceTop

4 楼lsqkeke（可可）回复于 2006-03-14 16:00:18 得分 0

declare @sql varchar(2000)
set @sql=''
select @sql=@sql+',['+a.[fsize]+']=sum(case when [fsize]='''+a.[fsize]+''' then [fqty] else 0 end)'
from tblsizeQtyn a,tblsizeelem b
where a.Fsize=b.Fsize
group by a.[fsize]
order by b.fsequence

set @sql='select farea,fres,fcomkey,Ftype '+@sql+'from tblsizeQty group by [farea], [fres], [fcomkey], [ftype]'
exec(@sql)Top

5 楼rivery（river）回复于 2006-03-14 16:05:15 得分 0

/*
如果报表是取所有tblsizeelem中的行作为列。那么直接从表tblsizeelem中取就可以了。
如果是仅取有数据存在的对应代码，则需要按照楼主原来的语句取tblsizeQty。
第一种情况如上所写进行排序。
第二种情况需要tblsizeelem与tblsizeQty连接来进行。
以上的基础都是需要fsize由fsequence唯一确定顺序。
*/Top

6 楼gerrley（gerrley）回复于 2006-03-14 16:23:14 得分 0

lsqkeke(可可) 提示:資料行名稱 'b.FSEQUENCE' 在 ORDER BY 子句中無效，因為它並未包含在彙總函數或 GROUP BY 子句中。
rivery(river): tblsizeelem中的Fsize有很多,我只是列了其中一部分出来,转换时只需要转换tblsizeQty有的SIZETop

7 楼gerrley（gerrley）回复于 2006-03-14 16:28:56 得分 0

rivery(river): 是仅取有数据存在的对应代码，但排序要按tblsizeelem的fsequence排,不与tblsizeelem关联,排序是乱的,但像这样写:
select @sql=@sql+',['+a.[fsize]+']=sum(case when [fsize]='''+a.[fsize]+''' then [fqty] else 0 end)'
from tblsizeQtyn a,tblsizeelem b
where a.Fsize=b.Fsize
group by a.[fsize]
order by b.fsequence
执行提示:伺服器: 訊息 8127，層級 16，狀態 1，行 3
資料行名稱 'b.FSEQUENCE' 在 ORDER BY 子句中無效，因為它並未包含在彙總函數或 GROUP BY 子句中。Top

8 楼gerrley（gerrley）回复于 2006-03-14 18:14:45 得分 0

select @sql=@sql+',['+a.[fsize]+']=sum(case when [fsize]='''+a.[fsize]+''' then [fqty] else 0 end)'
from tblsizeQtyn a,tblsizeelem b
where a.Fsize=b.Fsize
group by a.[fsize]
--order by b.fsequence
注释了order by b.fsequence后就可以执行,但就没按fsequence排序了,要怎么解决啊?Top

9 楼lsqkeke（可可）回复于 2006-03-14 18:58:40 得分 80

create table tblsizeelem(Fsize varchar(5), fsequence int)
insert tblsizeelem
select 'S' , 1 union all
select 'M' , 2 union all
select 'L' , 3 union all
select 'XL' , 4 union all
select 'XXL', 5

create table tblsizeQty(farea varchar(10), fres varchar(10), fcomkey int ,fsize varchar(5),ftype varchar(10), fqty int)
insert tblsizeQty
select 'A', 'Res1', 1000 , 'S' , 'Order1' , 100 union all
select 'A', 'Res1', 1000 , 'M' , 'Order1' , 103 union all
select 'A', 'Res1', 1000 , 'XL', 'Order2' , 102 union all
select 'B' , 'Res1', 1000 , 'S' , 'Order1' , 100 union all
select 'B', 'Res1', 1000 , 'M' , 'Order1' , 103 union all
select 'B' , 'Res1', 1000 , 'XXL', 'Order2' , 102

declare @sql varchar(2000)
set @sql=''
select @sql=@sql+',['+[fsize]+']=sum(case when [fsize]='''+[fsize]+''' then [fqty] else 0 end)'
from (select Fsize ,fsequence from tblsizeelem where Fsize in(select distinct Fsize from tblsizeQty))t
order by fsequence

set @sql='select farea,fres,fcomkey,Ftype '+@sql+'from tblsizeQty group by [farea], [fres], [fcomkey], [ftype]'
exec(@sql)

Top

10 楼lsqkeke（可可）回复于 2006-03-14 18:59:48 得分 0

经过测试，上面的SQL就可以实现了Top

11 楼gerrley（gerrley）回复于 2006-03-14 19:19:19 得分 0

谢谢!Top