SQL case statement when duplicate rows

Goal: col: id needs to be unique, no duplicates

Issue: when id_catelog1 is null, id_catelog2 kicks in but makes multiple rows since some of them have multiple id_catelog2.

For the multiple ones, I need it to put “multiple” under id_catelog_final instead of id_catelog2.

This is my query so far:

select distinct
    id,
    name,
    id_catelog1,
    Large_id_catelog2,
    case 
        when id_catelog1 is null 
            then id_catelog2 
            else id_catelog1 
    end as id_catelog_final
from
    table
where 
    id is not null;

Answer

I think you want aggregation with conditional logic:

select id, name,
       (case when max(coalesce(id_catelog1, Large_id_catelog2)) =
                  min(coalesce(id_catelog1, Large_id_catelog2))
             then min(coalesce(id_catelog1, Large_id_catelog2))
             else 'multiple'
         end)                  
from t
group by id, name