请问楼主,如下方式不行么?
测试:
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)
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)
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
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)
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)
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'