行转列问题
已知
Table1
Name A B C
A1 AA AB AC
A1 AD AE AF
A1 AG AH AI
A1 AJ AK AL
B2 BA BC BD
B2 BE BF BG
C1 CA CB CD

求
Table2
Name A1 B1 C1 A2 B2 C2 A3 B3 C3 A4 B4 C4
A1 AA AB AC AD AE AF AG AH AI AJ AK AL
B2 BA BC BD BE BF BG
C1 CA CB CD


------解决方案--------------------
create table Table1(name varchar(10),A varchar(10),B varchar(10),C varchar(10))
insert into Table1
select 'A1','AA','AB','AC' union all
select 'A1','AD','AE','AF' union all
select 'A1','AG','AH','AI' union all
select 'A1','AJ','AK','AL' union all
select 'B2','BA','BC','BD' union all
select 'B2','BE','BF','BG' union all
select 'C1','CA','CB','CD'
GO

select id=identity(int,1,1),*,tmp=0 
into tmp_t
from Table1
order by name

update A
set tmp=(select count(*) from tmp_t
where name=A.name and id<=A.id)
from tmp_t A

declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',max(case when tmp='+rtrim(tmp)+' then A end) as [A'+rtrim(tmp)+'],max(case when tmp='+rtrim(tmp)+' then B end) as [B'+rtrim(tmp)+'],max(case when tmp='+rtrim(tmp)+' then C end) as [C'+rtrim(tmp)+']'
from tmp_t
group by tmp
select @sql='select name'+@sql+' from tmp_t group by name'
exec (@sql)


drop table tmp_t,Table1