我的表是这样子的
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,[email protected]
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)GOINSERT INTO TABLE4SELECT '001',3 UNIONSELECT '001',4 UNIONSELECT '001',5 UNIONSELECT '002',5 UNIONSELECT '002',2 UNIONSELECT '002',3select Id,[1] AS score1, [2] AS score2,[3] AS score3from (SELECT ID,ds = ROW_NUMBER() over (PARTITION BY ID order by id,score),scorefrom TABLE4) AS Epivot (max(score) for ds in([1],[2],[3])) as d
------解决方案--------------------