sql一列多行转为一行多列

[秦拿希]

有这样的需求

表名test
id value
-------------
1 a
1 b
1 c
1 d
1 e

要把上面的结果集转为一行多列，如下
id value1 value2 value3 value4 value5
1 a b c d e

普通sql无法处理，求教大神，写存储过程拼接sql是否可以，如何实现？提供思路，谢谢

[唐诗三百首]

create table test
(id int, value varchar(5))

insert into test
 select 1, 'a' union all
 select 1, 'b' union all
 select 1, 'c' union all
 select 1, 'd' union all
 select 1, 'e'


select id,value1=[1],value2=[2],value3=[3],value4=[4],value5=[5]
  from (select id,value,row_number() over(partition by id order by getdate()) 'rn' from test) a
  pivot(max(value) for rn in ([1],[2],[3],[4],[5])) p

/*
id          value1 value2 value3 value4 value5
----------- ------ ------ ------ ------ ------
1           a      b      c      d      e

(1 row(s) affected)
*/

[Ekun_sky]

use master
go
if object_id('test') is not null drop table test
    create table test(id int, value varchar(15))
       insert into test
            select 1, 'a' union all
            select 1, 'b' union all
            select 1, 'c' union all
            select 1, 'd' union all
            select 1, 'e'
   
  declare @i varchar(500),@j varchar(500)='' 
 
 select @i=isnull(@i,'')+',['+ltrim(aid)+']' from 
 (select ROW_NUMBER()over(order by getdate()) as aid,* from test) as b 
 
set @j='select * from 
(select ROW_NUMBER()over(order by getdate()) as aid,* from test) as b pivot(max(value) for aid in ('+
stuff(@i,1,1,'')+')) as c'

exec(@j)

/*
----------------------
1	a	b	c	d	e
-----------------------
*/
 
 

[中国风]

create table #test
(id int, value varchar(5))
 
insert into #test
 select 1, 'a' union all
 select 1, 'b' union all
 select 1, 'c' union all
 select 1, 'd' union all
 select 1, 'e' union all
 select 2, 'b' union all
 select 2, 'c' union all
 select 2, 'd'
 
 go
 declare @Sql nvarchar(max)='',@Cols varchar(3);
 select top 1 @Cols=count(*) from #test group by ID order by count(*) desc;
 while @Cols>0
	select @Sql=',[value'+@Cols+']=max(case when RN='+@Cols+' then value else '''' end )'+@Sql,@Cols=@Cols-1

exec('select ID'+@Sql+' from (select *,RN=ROW_NUMBER()over(partition by ID order by ID) from #test) as T group by ID')
/*
ID	value1	value2	value3	value4	value5
1	a	b	c	d	e
2	b	c	d		
*/

[fishparadise]

都给版主们包了。

[wang_zhm]

太棒了，解决了我的大问题啊，再次感谢楼上各位哈！