行转列 ，分组取最大，统计数量

drop table  t;
create table t(
       c_1 varchar2(200),
       c_2 varchar2(200)
);
select * from t;
with t2 as 
(select c_1, c_2,row_number() over(partition by c_1,c_2 order by c_1,c_2) c_3 from t)
select c_1 单位,sum(decode(c_2,'1',c_3,0)) 类型等于1的个数,sum(decode(c_2,'2',c_3,0)) 类型等于2的个数 from (
select * from t2 m1
where not exists (
select 1 from t2 m2 where m1.c_1 = m2.c_1 and m1.c_2 = m2.c_2 and m1.c_3 < m2.c_3
)
order by c_1,c_2,c_3 desc
)
group by c_1
order by c_1