SQL – Adding columns

I am new to SQL and was wondering if someone can help me with this small request. I am trying to have my output like down below:

But I am currently getting this output:

Here is my SQL code:

SELECT trk_id, 
       cur_state
FROM trk_id_def
Where (trk_id like 'TPA12%' )
ORDER BY trk_id

How can I get the columns to reflect MF, CH_A, CH_B, and CH_C with its respective value?

Answer

@Med2020 Your query should work fine. Please look into below example and let me know for which data it’s failing. I have changed the condition for M column.

Schema and insert statements:

 create table trk_id_def( TRK_ID varchar(50),CUR_STATE varchar(50));

 insert into trk_id_def values('TPA12','PROD');
 insert into trk_id_def values('TPA12-A','PROD');
 insert into trk_id_def values('TPA12-B','NM');
 insert into trk_id_def values('TPA12-C','NM');

Query:

   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);

Output:

MISTI	M	CH_A	CH_B	CH_C
TPA12	PROD	PROD	NM	NM

db<fiddle here