how can I can running status from my table?

Question

I have a below table which has multiple rows with same executionid and different status. How can I get the row which status is running, rows will be exclude if executionid associated with both running and completed status?

Below image is the sample data :

Expected result should be:

Answer 1

• Using String_AGG() to get all the status per execution id into one list
• Joining that list to the original data and then filtering out the results using NOT LIKE operator, if there are cases that are completed and running then they are not selected

with all_status as (
Select

execution_id,
STRING_AGG (status,', ') as all_status_per_id
from [table]
)

Select 
data.*,
all_status.all_status_per_id
from [table] as data
left join all_status 
on data.execution_id = all_status.execution_id
where (all_status.all_status_per_id LIKE '%running%' AND all_status.all_status_per_id NOT LIKE '%completed%')
AND status = 'running'