Merge multiple rows with same ID but different data into one row

How can I merge multiple rows with same ID but different data into one row.

I have table:

ORDER_ID  MATERIAL  PLAN_QTY    STATUS
3         A         13          OPEN
3         B         13          OPEN
3         C         13          OPEN
3         D         13          CLOSE
3         E         13          OPEN
3         F         13          OPEN
3         G         13          OPEN
4         A         1000        OPEN
4         B         1000        OPEN
4         C         1000        OPEN
4         D         1000        OPEN
4         E         1000        OPEN
4         F         1000        OPEN
4         G         1000        OPEN
5         A         500         CLOSE
5         B         500         CLOSE
5         C         500         CLOSE
5         D         500         CLOSE
5         E         500         CLOSE
5         F         500         CLOSE
5         G         500         CLOSE

I want to get table like this :

ORDER_ID    PLAN_QTY    STATUS
3           13          IN-PROGRESS
4           1000        OPEN
5           500         CLOSE

2 answers

  • answered 2018-04-17 04:18 Yogesh Sharma

    Use group by clause with conditional aggregation

    select ORDER_ID, PLAN_QTY,
           (case when count(distinct STATUS) > 1 then 'IN-PROGRESS' else             
           (select top 1 STATUS from table where ORDER_ID = t.ORDER_ID) end) as STATUS 
    from table t
    group by ORDER_ID, PLAN_QTY;    
    

    In other way use only aggregation function

    select ORDER_ID, PLAN_QTY,
           (case when max(STATUS) <> min(STATUS) 
                 then 'IN-PROGRESS' else max(STATUS) end) as STATUS 
    from table t
    group by ORDER_ID, PLAN_QTY;    
    

  • answered 2018-04-17 04:18 SouravA

    Try this:

    SELECT ORDER_ID, PLAN_QTY, 
    CASE WHEN MAX([STATUS]) = MIN([STATUS]) THEN MAX([STATUS]) ELSE 'IN-PROGRESS' END [Status])
    FROM tbl
    GROUP BY 
    ORDER_ID, PLAN_QTY;