sql 行转列语句(报表、统计经常用到)

create table student
(
 stuName varchar(50),
 [subject] varchar(50),
 exam int
)

go

insert into student values('scott','A',99)
insert into student values('scott','B',89)
insert into student values('scott','C',79)

insert into student values('tiger','A',79)
insert into student values('tiger','B',89)
insert into student values('tiger','C',99)

select * from student

select stuName,
sum(case subject when 'a' then exam else 0 end ) as A,
sum(case subject when 'b' then exam else 0 end ) as B,
sum(case subject when 'c' then exam else 0 end ) as C
from student group by stuName

select distinct s.stuName,
(select exam from student where stuName = s.stuName and [subject] = 'A' )as A,
(select exam from student where stuName = s.stuName and [subject] = 'B' )as B,
(select exam from student where stuName = s.stuName and [subject] = 'C' )as C
from student s

declare @s nvarchar(1000)
select @s = 'select stuName '
select @s = @s + ',sum(case when [subject]=''' + cast([subject] as varchar) + ''' then exam else 0 end) as '
+ [subject] from student group by [subject]
select @s = @s + ' from student group by [stuName]'
exec(@s)