如何多行转换多列

[marongc]

原数据表

1,A,B,C
2,A1,B1,C1
3,A2,B2,C2
4,E1,F1,G1
5,E2,F2,G2


想要转换的结果
1,A,B,C 4,E1,F1,G1
2,A1,B1,C1 5,E2,F2,G2
3,A2,B2,C2

[RINK_1]

DECLARE @SQL VARCHAR(8000) DECLARE @I INT DECLARE @N INT SELECT @I=COUNT(*) FROM TABLE SET @N=0 SET @SQL='FROM (SELECT * FROM TABLE WHERE (CAST(SUBSTRING(COLUMN1,1,CHARINDEX('','',COLUMN1)-1) AS INT)-1)/3='+CAST(@N AS VARCHAR)+') AS T'+CAST(@N AS VARCHAR) WHILE @I-3>0 BEGIN SET @N=@N+1 SET @SQL=@SQL+' LEFT JOIN (SELECT * FROM TABLE WHERE (CAST(SUBSTRING(COLUMN1,1,CHARINDEX('','',COLUMN1)-1) AS INT)-1)/3='+CAST(@N AS VARCHAR)+') AS T'+CAST(@N AS VARCHAR) +' ON (CAST(SUBSTRING(T'+CAST(@N-1 AS VARCHAR)+'.COLUMN1,1,CHARINDEX('','',T'+CAST(@N-1 AS VARCHAR)+'.COLUMN1)-1) AS INT)%3)=(CAST(SUBSTRING(T'+CAST(@N AS VARCHAR)+'.COLUMN1,1,CHARINDEX('','',T'+CAST(@N AS VARCHAR)+'.COLUMN1)-1) AS INT)%3)' SET @I=@I-3 END EXEC('SELECT * '+@SQL)

[code=perl][code=sql]

[/code][/code]

[道素]

你是要这样的？

if object_id('tempdb..#t') is not null drop table #t
create table #t(ID int,n varchar(10),m varchar(10),k varchar(10))
insert into #t(ID,n,m,k) 
select 1,'A','B','C' union all
select 2,'A1','B1','C1' union all
select 3,'A2','B2','C2' union all
select 4,'E1','F1','G1' union all
select 5,'E2','F2','G2'

declare @cols varchar(max),@sql varchar(max)
select @cols=isnull(@cols+',','')+'n'+ltrim(sv.number)+',m'+ltrim(sv.number)+',k'+ltrim(sv.number)
from master.dbo.spt_values as sv 
cross apply(select count(0)/3+case when count(0)%3=0 then 0 else 1 end from #t) c(n)
where sv.type='P' and sv.number between 1 and c.n
--select @cols
set @sql='select * from (
   select (ID-1)%3+1 as ID, c.t+ltrim((ID-1)/3+1) as col,c.v from #t
   cross apply(values(''n'',n),(''m'',m),(''k'',k))c(t,v)
) as t pivot(max(v) for col in('+@cols+')) p'
--select @sql
EXEC(@sql)

  	ID	n1	m1	k1	n2	m2	k2
1	1	A	B	C	E1	F1	G1
2	2	A1	B1	C1	E2	F2	G2
3	3	A2	B2	C2	NULL	NULL	NULL