Group products into one cell – Mysql

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 !

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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

Method 2

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]


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x