By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table trk_id_def( TRK_ID varchar(50),CUR_STATE varchar(50));
insert into trk_id_def values('TPA12','PROD');
1 rows affected
insert into trk_id_def values('TPA12-A','PROD');
1 rows affected
insert into trk_id_def values('TPA12-B','NM');
1 rows affected
insert into trk_id_def values('TPA12-C','NM');
1 rows affected
select substr(trk_id, 1, 5) as Misti,
max(case when trk_id =substr(trk_id, 1, 5) then cur_state end) as M,
max(case when trk_id like '%-A' then cur_state end) as CH_A,
max(case when trk_id like '%-B' then cur_state end) as CH_B,
max(case when trk_id like '%-C' then cur_state end) as CH_C
from trk_id_def Where (trk_id like 'TPA12%' )
group by substr(trk_id, 1, 5);
| MISTI | M | CH_A | CH_B | CH_C |
|---|---|---|---|---|
| TPA12 | PROD | PROD | NM | NM |