Combine multiple numeric values is one cell

Question

I need help with my query to Combine multiple numeric values is one cell

This is the main table:

Contact_ID	Order_id	Order_group
32221	122223	55555
32221	122224	55555
32221	122225	44444
32221	122226	44444
32221	122227	44444

My query:

 
SELECT
    Contact_ID,
    STRING_AGG( Order_group , ', ')  as 'ORDER GROUP'
FROM 
    Main 
 where Contact_ID = 32221 
GROUP BY Contact_ID

what I get:

Contact_ID	ORDER GROUP
32221	55555,55555,44444,44444, no end...

what I need is similar to this:

Contact_ID	ORDER GROUP
32221	55555,44444

how i can do that?

Answer 1

Try the following:

SELECT
    D.Contact_ID,
    STRING_AGG( D.Order_group , ', ')  as 'ORDER GROUP'
FROM 
    (
      SELECT DISTINCT Contact_ID, Order_group FROM  Main 
    ) D 
 where D.Contact_ID = 32221 
GROUP BY D.Contact_ID

See a demo.

You may also specify the order of the list using WITHIN GROUP as the following:

STRING_AGG( D.Order_group , ', ') WITHIN GROUP (ORDER BY Order_group DESC) as 'ORDER GROUP'

Answer 2

You could apply a group by to the original set to build a distinct contactid and group order data set.

Then apply you string_agg to the outer query

SELECT
t2.Contact_ID
, STRING_AGG(t2.Order_group , ', ')  as 'ORDER GROUP'
FROM
(

SELECT
    Contact_ID
   , Order_group
   
FROM 
    Main 
 where Contact_ID = 32221 
GROUP BY Contact_ID , Order_group

) t2

GROUP BY t2.Contact_ID