Group products into one cell - Mysql-CodePudding

I've this table Orders :

 ----- -------------- 
| id_order | products| 
 ---------- --------- 
|  1  |    product 1  | 
 ----- -------------- 
|  1  |    product 49| 
 ----- -------------- 
|  1  |    product 12| 
 ----- -------------- 
|  2  |    Product 1 | 
 ----- -------------- 
|  3  |    Product 50| 
 ----- -------------- 
|  4  |    Product 42| 
 ----- --------------

In my SQL query I want to group products like this :

  ----- ------------------------------------- 
| id_order | products                        | 
 ---------- --------------------------------- 
|  1  |    product 1, product 49, product 12  | 
 ----- -------------------------------------- 
|  2  |    Product 1                         | 
 ----- -------------------------------------- 
|  3  |    Product 50                        | 
 ----- -------------------------------------- 
|  4  |    Product 42                        | 
 ----- --------------------------------------

How can I regroup these products in one cell ? using GROUP_CONCAT ?

Thanks for your help !

CodePudding user response：

Here I'm using GROUP_CONCAT which is inbuilt method in Mysql, this only works for Mysql server.

-- create
CREATE TABLE Products (
  Id INTEGER,
  product TEXT NOT NULL
);

-- insert
INSERT INTO Products VALUES (1, 'Product 1');
INSERT INTO Products VALUES (1, 'Product 2');
INSERT INTO Products VALUES (1, 'Product 3');
INSERT INTO Products VALUES (2, 'Product 9');
INSERT INTO Products VALUES (3, 'Product 10');

-- fetch 
SELECT Id, GROUP_CONCAT(product) FROM Products GROUP BY Id

Output:

1|Product 1,Product 2,Product 3
2|Product 9
3|Product 10

CodePudding user response：

You can try using STRING_AGG() grouping function if available on Your DB.

Check here: [https://stackoverflow.com/questions/69484961/join-comment-rows-into-single-row/69487705#69487705][1]