将union all与聚合一起使用。
演示：

with your_table as (
select stack(4,
'tom'  ,'A','A','B',
'tom'  ,'B','A','C',
'peter','B','C','C',
'peter','A','B','C'
) as (name,chinese,math,english)
)

select name, 'chinese' as object, 
       count(case when chinese='A' then 1 end) as A, 
       count(case when chinese='B' then 1 end) as B,
       count(case when chinese='C' then 1 end) as C
  from your_table 
 group by name
    UNION ALL 
select name, 'math' as object, 
       count(case when math='A' then 1 end) as A, 
       count(case when math='B' then 1 end) as B,
       count(case when math='C' then 1 end) as C
  from your_table 
 group by name
    UNION ALL 
select name, 'english' as object, 
       count(case when english='A' then 1 end) as A, 
       count(case when english='B' then 1 end) as B,
       count(case when english='C' then 1 end) as C
  from your_table 
 group by name;

结果：

name    object  a       b       c
peter   chinese 1       1       0
tom     chinese 1       1       0
peter   math    0       1       1
tom     math    2       0       0
peter   english 0       0       2
tom     english 0       1       1