典型行转列,请高手赐教,快速散分结贴!

[Leedye123]

当前数据：

AID       BID      Bname     AKey    AValue   
F320      4C61      ZS        1        P1
F320      9FB1      LS        2        P2
F320      4C61      ZS        2        P2
F320      4C61      ZS        3        P3

想要结果:

AID    BID      Bname   AKey1  AKey2  AKey3   AValue1  AValue2  AValue3  
F320   4C61      ZS      1      2      3        P1     P2      P3
F320   9FB1      LS      0      2      0        0      P2      0  

即：一.将相同BID的多行转为转为一行多列,(结果显示以同一BID最多行的Akey和AValue为准[如上面的最多BID为4C61:3个Akey和3个AValue都显示出来(Akey列名最好是Akey加上下标序数,如:Akey1,Akey2,Akey3...；Avalue也如此.)]; 二.且最多BID的行数不确定(同一BID>=N行),其它的BID:AKey和AValue存在值的按对应的位置显示,无值的以0来替代.)（最好是标准TSQL,准备移植到oracle中(直接用oracle语法更好咯),谢谢各位高手了！）

[中国风]

这样用

select 
AID
,BID
,Bname
,max(case when AKey=1 then AKey else 0 end) as AKey1
,max(case when AKey=2 then AKey else 0 end) as AKey2
,max(case when AKey=3 then AKey else 0 end) as AKey3
,max(case when AKey=1 then AValue else '0' end) as AValue1
,max(case when AKey=2 then AValue else '0' end) as AValue2
,max(case when AKey=3 then AValue else '0' end) as AValue3
from table1
group by AID,BID,Bname

[中国风]

行列互转_整理贴3 http://bbs.csdn.net/topics/240002706

[Leedye123]

谢谢roy_88大大,主要是数据的行数是不确定的，以上我主要是例举了3条(3个Akey和AValue,即有可能是1条，或是16条)，能否根据动态行数来得出结果呢？ 您用了max(case）(或用pivot应该也可以吧[Ora11g也支持的]，oracle 用 max decode也行) ，期待中...

[Leedye123]

大大们继续哦

[中国风]

oracle动态行转列参照 http://bbs.csdn.net/topics/330039676

[唐诗三百首]

TSQL的方法,动态SQL.

create table lee
(AID varchar(10), BID varchar(10), Bname varchar(5), AKey int, AValue varchar(5))

insert into lee
 select 'F320', '4C61', 'ZS', 1, 'P1' union all
 select 'F320', '9FB1', 'LS', 2, 'P2' union all
 select 'F320', '4C61', 'ZS', 2, 'P2' union all
 select 'F320', '4C61', 'ZS', 3, 'P3'


declare @tsql varchar(6000)

select @tsql=isnull(@tsql,'select AID,BID,Bname,')
             +'max(case when AKey='+rtrim(number)+' then AKey else 0 end) ''AKey'+rtrim(number)+''','
 from master.dbo.spt_values
 where [type]='P' 
 and number between 1 and (select max(AKey) from lee)

select @tsql=@tsql
             +'max(case when AKey='+rtrim(number)+' then AValue else ''0'' end) ''AValue'+rtrim(number)+''','
 from master.dbo.spt_values
 where [type]='P' 
 and number between 1 and (select max(AKey) from lee)

select @tsql=left(@tsql,len(@tsql)-1)+' from lee group by AID,BID,Bname '

exec(@tsql)

/*
AID        BID        Bname AKey1       AKey2       AKey3       AValue1 AValue2 AValue3
---------- ---------- ----- ----------- ----------- ----------- ------- ------- -------
F320       4C61       ZS    1           2           3           P1      P2      P3
F320       9FB1       LS    0           2           0           0       P2      0

(2 row(s) affected)
*/

[Leedye123]

嗯，以上参考之前看过了,我也再想想，也期待大大们继续讨论哦

[Leedye123]

期待高手出现