sql 纵向读成横向

[mmkkuoi]

create table test
(
id int identity (1,1) primary key,
name nvarchar(50),
kemu nvarchar(50)
)

insert into test values('a','语文'),('a','语文'),('a','数学') ,('b','语文'),('b','语文'),('c','语文');

select * fromt test;

希望得到
姓名 语文 数学
a 2 1
b 2 0
c 1 0

[挨踢直男]

select 
name 姓名,
count(case kemu when '语文' then 1 else null end) 语文,
count(case kemu when '数学' then 1 else null end) 数学
from test
group by name

[AcHerat]

create table test
(
id int identity (1,1) primary key,
name nvarchar(50),
kemu nvarchar(50)
)

insert into test values('a','语文')
insert into test values('a','语文')
insert into test values('a','数学')
insert into test values('b','语文')
insert into test values('b','语文')
insert into test values('c','语文')

declare @sql varchar(1000)
set @sql = 'select name'
select @sql = @sql + ',sum(case kemu when ''' + kemu + ''' then 1 else 0 end) [' + kemu + ']'
from (select distinct kemu from test)t
select @sql = @sql + ' from test group by name'
exec(@sql)

drop table test


/**********************************
name                                               数学          语文
-------------------------------------------------- ----------- -----------
a                                                  1           2
b                                                  0           2
c                                                  0           1

(3 行受影响)