create table t1 (c1 varchar2(5),c2 varchar2(5));
insert into t1 values ('a','t');
insert into t1 values ('a','t');
insert into t1 values ('a','t');
insert into t1 values ('a','t');
insert into t1 values ('a','w');
insert into t1 values ('a','w');
insert into t1 values ('a','w');
insert into t1 values ('b','w');
insert into t1 values ('c','t');
insert into t1 values ('c','t');
insert into t1 values ('c','w');
commit;

select c1,
       't' t,
       sum(decode(c2,'t',1,0)) t_ct,
       'w' w,
       sum(decode(c2,'w',1,0)) w_ct
from t1
group by c1

     c1    t    t_ct    w    w_ct
-----------------------------------------------
1    a    t    4    w    3
2    b    t    0    w    1
3    c    t    2    w    1

------解决方案--------------------
SQL code

select c1,wmsys.wm_concat(cnt) cnt from(
select c1,c2||'_'||max(rn) cnt from (
select t.c1,t.c2,count(1) over(partition by c1,c2 order by t.c1) rn  from t1 t
 )tab 
group by c1,c2 ) tab1
group by c1