行转列~~~
有表T_A:
AID AName
1 红
2 黄
表T_B:
BID A BName BShowOrder
1 1 E 1
2 1 D 2
3 1 V 3
4 2 X 1
5 2 J 2
6 2 D 3
7 2 E 4
8 2 C 5
请问,如何才能得到如下表:
AID AName BName1 BName2 BName3 BName4 BName5
1 红 E D V
2 黄 X J D E C
[解决办法]
declare @sql varchar(4000)
set @sql= ' '
select @sql+ ',[BName '+rtrim(BShowOrder)+ ']=max(case when BShowOrder= '+rtrim(BShowOrder)+
' then BName end) '
from t_b
group by BShowOrder
exec( 'select AName '+@sql+ ' from t_a join t_b on t_a.AID=tb.A group by AName ')
[解决办法]
create table T_A(AID int,AName varchar(10))
insert into t_a(aid,aname) values(1, '红 ')
insert into t_a(aid,aname) values(2, '黄 ')
create table t_b(BID int,A int,BName varchar(10),BShowOrder int)
insert into t_b(BID ,A ,BName ,BShowOrder ) values(1, 1, 'E ', 1 )
insert into t_b(BID ,A ,BName ,BShowOrder ) values(2, 1, 'D ', 2)
insert into t_b(BID ,A ,BName ,BShowOrder ) values(3, 1, 'V ', 3)
insert into t_b(BID ,A ,BName ,BShowOrder ) values(4, 2, 'X ', 1)
insert into t_b(BID ,A ,BName ,BShowOrder ) values(5, 2, 'J ', 2)
insert into t_b(BID ,A ,BName ,BShowOrder ) values(6, 2 , 'D ', 3)
insert into t_b(BID ,A ,BName ,BShowOrder ) values(7, 2, 'E ', 4)
insert into t_b(BID ,A ,BName ,BShowOrder ) values(8, 2, 'C ', 5)
go
declare @sql varchar(8000)
set @sql = 'select A ,aname '
select @sql = @sql + ' , max(case BShowOrder when ' ' ' + cast(BShowOrder as varchar(10)) + ' ' ' then BName else ' ' ' ' end) [ ' + 'bname ' + cast(BShowOrder as varchar(10)) + '] '
from (select distinct BShowOrder from (select a.* , b.* from t_a a, t_b b where a.aid = b.a) m) as t
set @sql = @sql + ' from (select a.* , b.* from t_a a, t_b b where a.aid = b.a) t group by A ,aname '
exec(@sql)
drop table t_a,t_b
/*
A aname bname1 bname2 bname3 bname4 bname5
----------- ---------- ---------- ---------- ---------- ---------- ----------
1 红 E D V
2 黄 X J D E C
*/
