how to divide string in a column to be a column name in redshift

Question

I have built this query below in order to get the total vol by each route, sla_min and also the sla_status.

The sla_status is calculated with case when syntax to get the over sla and also meet sla

with data_manifest as (
select no,
       concat(concat(origin,'-'),destination) as route_city, 
       sla_min,
       case
           when status>0 and datediff(day, sla_max_date_time_internal, last_valid_tracking_date_time) > 0 then 'OVER SLA'
           when status=0 and datediff(day, sla_max_date_time_internal, current_date) > 0 then 'OVER SLA' else 'MEET SLA'
        end as status_sla
from data
where trunc(tgltransaksi::date) between ('30 January,2023') and ('9 February,2023')
), data_vol as (
select
    route_city,
    count(distinct no) as volume,
    status_sla,
    sla_min,
from data_manifest
group by route_city, status_sla, sla_min
)

The query results this:

route_city     vol      status_sla      sla_min
A - B          20        MEET SLA          2
A - B          40        OVER SLA          2
B - C          30        MEET SLA          1
B - C          30        OVER SLA          1

my question is how can I split the MEET SLA and OVER SLA become the column names so the structure would be like this:

route_city    MEET SLA   OVER SLA   total_vol    sla_min
A - B          20           40         60           2
B - C          30           30         60           1 

how should I write the query to get the desired result in redshift?

thank you in advance

Answer 1

Not seeing your input data it isn't clear what exactly you need but here's a shot.

You need to stop grouping by status_sla and count the number for each value of status_sla.

with data_manifest as (
select no,
       concat(concat(origin,'-'),destination) as route_city, 
       sla_min,
       case
           when status>0 and datediff(day, sla_max_date_time_internal, last_valid_tracking_date_time) > 0 then 'OVER SLA'
           when status=0 and datediff(day, sla_max_date_time_internal, current_date) > 0 then 'OVER SLA' else 'MEET SLA'
        end as status_sla
from data
where trunc(tgltransaksi::date) between ('30 January,2023') and ('9 February,2023')
), data_vol as (
select
    route_city,
    count(distinct no) as volume,
    count(distinct decode(status_sla, 'MEET SLA', no, NULL)) as meet_sla,
    count(distinct decode(status_sla, 'OVER SLA', no, NULL)) as over_sla,
    sla_min,
from data_manifest
group by route_city, sla_min
)

There are other ways of doing this that might work betting for the edge cases. Not knowing what these are results in this minimal change approach.

Above code is untested.