Merge SQL Columns & Data with same prod_id

user_3155527 0
Viewed 55

I have a table that looks like this:

| id   | product | prod_id |
| 1001 | Item 1  | P1001   |
| 1002 | Item 2  | P1001   |
| 1003 | Item 3  | P1003   |

My desired output is:

| id   | prod_id        |
| 1001 | Item 1, Item 2 |
| 1003 | Item 3         |

As you can see, Item 1 and 2 has the same prod_id.

This is what I tried:

select id, prod_id from table_name where prod_id = product;

Basically what I tried was to merge all the products with the same prod_id and then just display them as id & prod_id.

I am new to mySQL and would love some feedback on how I can do this. I am not sure if my logic is correct in trying to fix the issue.

mysql sql
1 Answers

You can try to use aggregate function MIN with GROUP_CONCAT

Schema (MySQL v5.7)

CREATE TABLE T(
  id int,
  product varchar(50),
  prod_id varchar(50)
);


INSERT INTO T VALUES (1001,'Item 1','P1001');
INSERT INTO T VALUES (1002,'Item 2','P1001');
INSERT INTO T VALUES (1003,'Item 3','P1003');

Query #1

SELECT MIN(id) id,GROUP_CONCAT(product) prod_id        
FROM T
GROUP BY prod_id;

| id   | prod_id       |
| ---- | ------------- |
| 1001 | Item 1,Item 2 |
| 1003 | Item 3        |

View on DB Fiddle

user_51760710
Related