我有一个SQL查询语句的问题
我的表是这样子的
 id score
001 3
001 4
001 5
002 5
002 2 
002 3
.....
每一个ID有三条记录
我想转成一这样的样子
id score1 score2 score3
001 3 4 5
002 5 2 3
....
这个问题我想了很久一直没做出来，大家能帮我想一下吗？谢谢


------解决方案--------------------
select '001' id, 3 score into #aa
union all select '001', 4 score
union all select '001', 5 score
union all select '002', 5 score
union all select '002', 2 score
union all select '002', 3 score

select id,MIN(score1) score1,MIN(score2) score2,MIN(score3) score3 
from (select id,case when rid=1 then score end score1,case when rid=2 then score end score2,case when rid=3 then score end score3
from (select ROW_NUMBER() OVER(PARTITION BY id order by id) rid,* from #aa) bb) cc
group by id
------解决方案--------------------
select 0 B,id,score into #tmp
from table order by id

declare @b int
declare @id varchar(10)

set @b=0
set @id='001'

update #tmp set @b=case when @id = id then @b+1 else 1 end,
@id =case when @id =id then @id else id end,b=@b
from #tmp

select id,sum(case when b=1 then score else 0 end) score1,
sum(case when b=2 then score else 0 end) score2,
sum(case when b=3 then score else 0 end) score3
from #tmp
group by id
------解决方案--------------------
SQL Server 2005以上

SQL code

CREATE TABLE TABLE4
(
    Id VARCHAR(10),
    score INT
)

GO

INSERT INTO TABLE4
SELECT '001',3 UNION
SELECT '001',4 UNION
SELECT '001',5 UNION
SELECT '002',5 UNION
SELECT '002',2 UNION
SELECT '002',3

select Id,[1] AS score1, [2] AS score2,[3] AS score3
from (SELECT ID,ds = ROW_NUMBER() over (PARTITION BY ID order by id,score),score
from TABLE4) AS E
pivot (max(score) for ds in([1],[2],[3])) as d

------解决方案--------------------
探讨


SQL Server 2005以上
SQL code

CREATE TABLE TABLE4
(
    Id VARCHAR(10),
    score INT
)

GO

INSERT INTO TABLE4
SELECT '001',3 UNION
SELECT '001',4 UNION
SELECT '001',5 UNION
SELECT '002',5 UNION
SEL……