select 姓名,
sum(case when field1='铬版' then 分数 else 0 end) as 铬版,
sum(case when field1='凸版' then 分数 else 0 end) as 凸版,
sum(case when field1='菲林' then 分数 else 0 end) as 菲林,
sum(case when field1='干版' then 分数 else 0 end) as 干版
from tablename
group by 姓名
sum(case when field1='铬版' then 分数 else 0 end) as 铬版,
sum(case when field1='凸版' then 分数 else 0 end) as 凸版,
sum(case when field1='菲林' then 分数 else 0 end) as 菲林,
sum(case when field1='干版' then 分数 else 0 end) as 干版
from tablename
group by 姓名
from (select distinct name from OriTable) t inner join
(select name,lbnum from OriTable where type='铬版')o1 on t.name=o1.name
left join (select name,lbnum from OriTable where type='凸版)o2 on t.name=o2.name
left join (select name,lbnum from OriTable where type='菲林')o3 on t.name=o2.name
left join (select name,lbnum from OriTable where type='干版')o4 on t.name=o2.name
try it
http://expert.csdn.net/Expert/topic/1930/1930571.xml?temp=.4709436
假如你的记录很多时可以动态的查询:declare @y varchar(10)
declare @s varchar(8000)
set @s=''
declare a cursor for
select type from yourtable
open a
fetch next from a into @y
while @@fetch_status=0
begin
set @S=@s+',sum(case type when '''+@y+''' then count else 0 end) '''+@y+''''
fetch next from a into @y
end
close a
deallocate aexec('select name '+@s+' from yourtable group by name')
sum(case when field1='铬版' then 分数 else 0 end) as 铬版,
sum(case when field1='凸版' then 分数 else 0 end) as 凸版,
sum(case when field1='菲林' then 分数 else 0 end) as 菲林,
sum(case when field1='干版' then 分数 else 0 end) as 干版
from tablename
group by 姓名
凑个热闹。来晚了
sum(case when field1='铬版' then 分数 else 0 end) as 铬版,
sum(case when field1='凸版' then 分数 else 0 end) as 凸版,
sum(case when field1='菲林' then 分数 else 0 end) as 菲林,
sum(case when field1='干版' then 分数 else 0 end) as 干版
from tablename
group by 姓名