一个显示格式转换的题目请教各位高手!
有这么一个表
id         val
a           40
a           50
a           60
b           45
b           55
b           65
将其转换为下面格式
id         val1           val2         val3
a           40                 50             60
b           45                 55             65

请问应该怎么做？谢谢！

------解决方案--------------------
--如果每個ID只有三個，且ID相同的時候，val不會相同的話
Create Table TEST
(id Varchar(10),
val Int)
Insert TEST Select 'a ', 40
Union All Select 'a ', 50
Union All Select 'a ', 60
Union All Select 'b ', 45
Union All Select 'b ', 55
Union All Select 'b ', 65
GO
Select
id,
SUM(Case CountID When 1 Then val Else 0 End) As val1,
SUM(Case CountID When 2 Then val Else 0 End) As val2,
SUM(Case CountID When 3 Then val Else 0 End) As val3
From
(Select CountID = (Select Count(*) From TEST Where id = A.id And val <= A.val), * From TEST A) B
Group By
id
GO
Drop Table TEST
--Result
/*
id val1 val2 val3
a 40 50 60
b 45 55 65
*/
------解决方案--------------------
create table #tab1 (id int,val int)
inset into #tab1 values(a,40)
inset into #tab1 values(a,50)
inset into #tab1 values(a,60)
inset into #tab1 values(b,45)
inset into #tab1 values(b,55)
inset into #tab1 values(b,65)
go
inset into #tab2 select a as id, 40 as val1,50 as val2,60 as val3
union all
select b as id,45 as val1,55 as val2,65 as val3

select * from #tab2

drop table #tab1
drop table #tab2
------解决方案--------------------
if object_id( 'pubs..tb ') is not null
drop table tb
go

create table tb(id varchar(10),val int)
insert into tb(id,val) values( 'a ', 40)
insert into tb(id,val) values( 'a ', 50)
insert into tb(id,val) values( 'a ', 60)
insert into tb(id,val) values( 'b ', 45)
insert into tb(id,val) values( 'b ', 55)
insert into tb(id,val) values( 'b ', 65)
go

declare @sql varchar(8000)
set @sql = 'select id '
select @sql = @sql + ' , max(case px when ' ' ' + cast(px as varchar) + ' ' ' then val else 0 end) [ ' + 'val ' + cast(px as varchar) + '] '
from (select distinct px from (select px=(select count(1) from tb where id=a.id and val <a.val)+1 , * from tb a) t) as a
set @sql = @sql + ' from (select px=(select count(1) from tb where id=a.id and val <a.val)+1 , * from tb a) t group by id '
exec(@sql)

drop table tb

/*
id val1 val2 val3
---------- ----------- ----------- -----------
a 40 50 60
b 45 55 65
*/