How to convert rows to columns and find top string

Question

Need some help to solve below. I have a table:

expected result:

below logic should be applied by SQL. IF "Indicator" = T1, then need to find lowest "KEY-XXX-1", Column "IN/OUT" should be converted into separate Columns and insert the dates. If "Indicator" = T2, then need to find top "KEY-X-?" by last digits after '-', convert "IN/OUT" into separate Columns and take corresponding dates.

any help would be greatly appreciated!

Answer 1

Query:

select t.`KEY`, t.Indicator, 
substring_index(t.dates, ',', 1) as `Date IN`,
substring_index(t.dates, ',', -1) as `Date OUT`
from (
 select
  CONCAT(
   rs.first_key, '-',
   rs.middle_key, '-',
   case
    when rs.indicator = 'T1' then MIN(rs.last_key)
    when rs.indicator = 'T2' then MAX(rs.last_key)
    end
  ) as `KEY`,
  rs.indicator as Indicator,
  case 
   when rs.indicator = 'T1' then
    substring_index(group_concat(
     rs.date
     order by rs.last_key, rs.IN_OUT separator ','
    ), ',', 2)
   when rs.indicator = 'T2' then
    substring_index(group_concat(
     rs.date
     order by rs.last_key desc, rs.IN_OUT separator ','
    ), ',', 2)
  end as dates
  from(    
   select 
    substring_index(`Key`, '-', 1) as first_key,
    substring_index(substring_index(`Key`, '-', 2), '-', -1) as middle_key,
    substring_index(substring_index(`Key`, '-', 3), '-', -1) as last_key, 
    indicator,
    IN_OUT,
    date  
    from records
  ) as rs
  group by rs.first_key, rs.middle_key, rs.indicator
) as t;

Output: