CODEWITHSUNDEEP
sqlstringpostgresqlcsvcase
Peter Mølgaard Pallesen
Peter Mølgaard Pallesen

Reputation: 1931

Postgres perform aggregation on array or string_agg

I have a column which is a string of comma seperated elements like this statuses= 'Initial,Initial,Completed,InProgress. And I what to make a status column like this:

CASE
    WHEN <all statuses are 'Initial'> THEN 'Initial'
    WHEN <all statuses are 'Completed'> THEN 'Completed'
    ELSE 'InProgress'
END AS status

I have tried bool_and(string_to_array(statuses,',')='Initial'), which does not compile. Any suguestions?

Upvotes: 0

Views: 198

Answers (1)

GMB
GMB

Reputation: 222402

You could do this with string_to_array() and the ALL operator:

case 
    when 'Initial'   = ALL(string_to_array(statuses, ',')) then 'Initial'
    when 'Completed' = ALL(string_to_array(statuses, ',')) then 'Completed'
    else 'InProgress'
end status

Demo on DB Fiddle:

with t as (
    select 'Initial,Initial' statuses
    union all select 'Completed,Completed'
    union all select 'Initial,Completed,Other'
)
select 
    statuses,
    case 
        when 'Initial'   = ALL(string_to_array(statuses, ',')) then 'Initial'
        when 'Completed' = ALL(string_to_array(statuses, ',')) then 'Completed'
        else 'InProgress'
    end status
from t

statuses                | status    
:---------------------- | :---------
Initial,Initial         | Initial   
Completed,Completed     | Completed 
Initial,Completed,Other | InProgress

Upvotes: 1

Related Questions