select no,
typ1sc=isnull(sum(case type when 1 then score end),0),
typ2sc=isnull(sum(case type when 2 then score end),0),
typ3sc=isnull(sum(case type when 3 then score end),0),
typ4sc=isnull(sum(case type when 4 then score end),0)
from mybook group by no
typ1sc=isnull(sum(case type when 1 then score end),0),
typ2sc=isnull(sum(case type when 2 then score end),0),
typ3sc=isnull(sum(case type when 3 then score end),0),
typ4sc=isnull(sum(case type when 4 then score end),0)
from mybook group by no
Create table mybook
(no varchar(5),
type Int,
score Int
)
GO
--插入数据
Insert mybook Values('A', 3, 10)
Insert mybook Values('A', 1, 30)
Insert mybook Values('A', 2, 80)
Insert mybook Values('B', 2, 40)
Insert mybook Values('B', 3, 20)
Insert mybook Values('C', 1, 60)
Insert mybook Values('C', 2, 30)
Insert mybook Values('C', 3, 40)
Insert mybook Values('D', 4, 8)
Insert mybook Values('D', 3, 100)
Insert mybook Values('D', 1, 47)
Insert mybook Values('D', 2, 23)
GO
--测试
Select Distinct no,
IsNull((Select score from mybook Where type=1 And no=A.no),0) As typ1sc,
IsNull((Select score from mybook Where type=2 And no=A.no),0) As typ2sc,
IsNull((Select score from mybook Where type=3 And no=A.no),0) As typ3sc,
IsNull((Select score from mybook Where type=4 And no=A.no),0) As typ4sc
from mybook A
--删除测试环境
Drop table mybook
--结果
/*
no typ1sc typ2sc typ3sc typ4sc
A 30 80 10 0
B 0 40 20 0
C 60 30 40 0
D 47 23 100 8
*/
Create table mybook
(no varchar(5),
type Int,
score Int
)
GO
--插入数据
Insert mybook Values('A', 3, 10)
Insert mybook Values('A', 1, 30)
Insert mybook Values('A', 2, 80)
Insert mybook Values('B', 2, 40)
Insert mybook Values('B', 3, 20)
Insert mybook Values('C', 1, 60)
Insert mybook Values('C', 2, 30)
Insert mybook Values('C', 3, 40)
Insert mybook Values('D', 4, 8)
Insert mybook Values('D', 3, 100)
Insert mybook Values('D', 1, 47)
Insert mybook Values('D', 2, 23)
GO--测试
declare @str varchar(4000)select @str='select no, '
select @str=@str+'typ'+cast(type as varchar)+'sc=isnull(sum(case type when '+cast(type as varchar)+' then score end),0),'
from mybook group by type order by typeselect @str=left(@str,len(@str)-1)+' '
select @str=@str+'from mybook group by no'
print @str
exec (@str)
--删除测试环境
Drop table mybook
--结果
/*
no typ1sc typ2sc typ3sc typ4sc
A 30 80 10 0
B 0 40 20 0
C 60 30 40 0
D 47 23 100 8
*/