怎样将行转成列

[yangyangbeibei]

表a的结构如下
ID year score
001 1999 1
001 2000 2
001 2001 3
002 1999 1
怎样做才能将其转成如下格式
ID 1999 2000 2001
001 1 2 3
002 1

[txlicenhe]

TRY:
declare @sql varchar(8000)
set @sql = 'select ID'
select @sql = @sql + ',(case [year] when '''+[year]+''' then score else 0 end) ['+[year]+']'
from (select distinct [year] from 你的表) a

select @sql =@sql+' from 你的表 group by ID'

exec(@sql)

[yujohny]

declare @sql varchar(8000)
set @sql = 'select ID'
select @sql = @sql + ',sum(case [year] when '''+[year]+''' then score end) ['+[year]+']' from (select distinct [year] from 表a) as a
select @sql = @sql+' from 表a group by ID'

[txlicenhe]

create table a(id char(3),year int,score int)
insert a select '001', 1999, 1
union all select '001', 2000, 2
union all select '001', 2001, 3
union all select '002', 1999, 1


declare @sql varchar(8000)
set @sql = 'select distinct ID'
select @sql = @sql + ',sum(case [year] when '''+cast([year] as char(4))+''' then score else 0 end) ['+cast([year] as char(4))+']'
from (select distinct [year] from a) aa

select @sql =@sql+' from a group by ID '
--print @sql
exec(@sql)


ID 1999 2000 2001
---- ----------- ----------- -----------
001 1 2 3
002 1 0 0

[yujohny]

declare @sql varchar(8000)
set @sql = 'select ID'
select @sql = @sql + ',sum(case [year] when '''+[year]+''' then score end) ['+[year]+']' from (select distinct [year] from 表a) as a
select @sql = @sql+' from 表a group by ID'
exec(@sql)

[Wally_wu]

select distinct id, (
(case year when 1999 then score else 0 end) 1999,
(case zc when 2000 then score else 0 end) 2000,
(case zc when 2001 then score else 0 end) 2001
)
from table group by id

[txlicenhe]

create table a(id char(3),year int,score int)
insert a select '001', 1999, 1
union all select '001', 2000, 2
union all select '001', 2001, 3
union all select '002', 1999, 1


declare @sql varchar(8000)
set @sql = 'select distinct ID'
select @sql = @sql + ',IsNull(cast(sum(case [year] when '''+cast([year] as char(4))+''' then score end) as varchar(10)),'''') ['+cast([year] as char(4))+']'
from (select distinct [year] from a) aa

select @sql =@sql+' from a group by ID '
--print @sql
exec(@sql)


ID 1999 2000 2001
---- ---------- ---------- ----------
001 1 2 3
002 1

[yangyangbeibei]

表a的结构如下
ID year score
001 1999 1
001 2000 2
001 2001 3
002 1999 1
... ... .
怎样做才能将其转成如下格式
ID 1999 2000 2001 ...
001 1 2 3
002 1
... .
我想把提问改成这样,,,也可以做到吗???

[yangyangbeibei]

当然时间可以限定,,,比如从1999--2002

不过时间段是活动的,,,还可以写成2000--2002

[perfwell]

up

[yangyangbeibei]

时间是在程序里限定的,,,上面的方法我都试过了,,,不好用啊,,,

[txlicenhe]

请问楼主，如下方式不行么？
测试：
create table a(id char(3),year int,score int)
insert a select '001', 1999, 1
union all select '001', 2000, 2
union all select '001', 2001, 3
union all select '002', 1999, 1


declare @sql varchar(8000)
set @sql = 'select distinct ID'
select @sql = @sql + ',IsNull(cast(sum(case [year] when '''+cast([year] as char(4))+''' then score end) as varchar(10)),'''') ['+cast([year] as char(4))+']'
from (select distinct [year] from a) aa

select @sql =@sql+' from a group by ID '
--print @sql
exec(@sql)


ID 1999 2000 2001
---- ---------- ---------- ----------
001 1 2 3
002 1