select 姓名,
(case 类型 when '维修' then count(姓名) else 0 end) as 维修,
(case 类型 when '新装' then count(姓名) else 0 end) as 新装,
count(姓名) as 合计,
(case 评价 when '好' then count(姓名) else 0 end) as 好,
(case 评价 when '差' then count(姓名) else 0 end) as 差,
(case 评价 when '一般' then count(姓名) else 0 end) as 一般,
0 as 满意率
from 表1
group by 姓名
(case 类型 when '维修' then count(姓名) else 0 end) as 维修,
(case 类型 when '新装' then count(姓名) else 0 end) as 新装,
count(姓名) as 合计,
(case 评价 when '好' then count(姓名) else 0 end) as 好,
(case 评价 when '差' then count(姓名) else 0 end) as 差,
(case 评价 when '一般' then count(姓名) else 0 end) as 一般,
0 as 满意率
from 表1
group by 姓名
(
select 姓名,
维修=(select count(*)from tmp a where tmp.姓名=a.姓名 and 类型='维修'),
新装=(select count(*)from tmp a where tmp.姓名=a.姓名 and 类型='新装'),
好=(select count(*)from tmp a where tmp.姓名=a.姓名 and 评价='好'),
差=(select count(*)from tmp a where tmp.姓名=a.姓名 and 评价='差'),
一般=(select count(*)from tmp a where tmp.姓名=a.姓名 and 评价='一般')
from 表1
group by 姓名
)a
create table tt(a int identity(1,1),b varchar(20),c varchar(20),d varchar(20))
insert tt
select 'aaa' , '维修' , '好' union all
select 'bbb' , '维修' , '差' union all
select 'ccc' , '新装' , '好' union all
select 'bbb' , '新装' , '好' union all
select 'aaa' , '维修' , '一般'
--SQL语句,其中合计的满意率不知道你怎么算,就写为0
select 姓名,维修,新装,合计,好,一般,差,满意率=1.0*(好+一般)/(好+一般+差)
from
(select 姓名=b,
维修=sum(case c when '维修' then 1 else 0 end ),
新装=sum(case c when '新装' then 1 else 0 end ),
合计=count(c),
好=sum(case d when '好' then 1 else 0 end),
差=sum(case d when '差' then 1 else 0 end),
一般=sum(case d when '一般' then 1 else 0 end)
from tt
group by b) tunion allselect 姓名='合计',维修=sum(维修),新装=sum(新装),合计=sum(合计),
好=sum(好),一般=sum(一般),差=sum(差),满意率='0'
from
(select 姓名=b,
维修=sum(case c when '维修' then 1 else 0 end ),
新装=sum(case c when '新装' then 1 else 0 end ),
合计=count(c),
好=sum(case d when '好' then 1 else 0 end),
差=sum(case d when '差' then 1 else 0 end),
一般=sum(case d when '一般' then 1 else 0 end)
from tt
group by b) g
--结果:姓名 维修 新装 合计 好 差 一般 满意率aaa 2 0 2 1 1 0 1.000000000000
bbb 1 1 2 1 0 1 .500000000000
ccc 0 1 1 1 0 0 1.000000000000
合计 3 2 5 3 1 1 .000000000000