Combining strings together using mysql

Question

I want to combine multiple row values ​​from a table.

Table Name: Element

ID | item_id | Value 1 43 item1 2 44 item2 3 44 item3 4 44 item4 5 45 item5 6 45 item6 

The identifiers are unique (primary key) What I'm looking for for output is a mysql query that gives this result, as follows.

Output:

  ID | item_id | Value 1 43 item1 2 44 item2,item3,item4 3 44 item2,item3,item4 4 44 item2,item3,item4 5 45 item5,item6 6 45 item5,item6 

kindly asking for some suggestions

Answer 1

You can use self join with the group_concat subquery

 SELECT i.ID, i.item_id, subquery.`new_value` as value FROM item i JOIN ( SELECT item_id, GROUP_CONCAT(`Value`) as new_value FROM item GROUP BY item_id; )as subquery ON subquery.item_id = i.item_id; 

Answer 2

Try this query:

 SELECT t1.ID, t1.item_id, t2.Value FROM item t1 INNER JOIN ( SELECT item_id, GROUP_CONCAT(Value) AS Value FROM item GROUP BY item_id ) t2 ON t1.item_id = t2.item_id 

Follow the link below for a demo version:

SQLFiddle

Answer 3

You can use the query below.

 SELECT GROUP_CONCAT(value) FROM Item GROUP BY item_id; 

Answer 4

Try this request

  SELECT t1.ID, t1.item_id, ( SELECT GROUP_CONCAT(t2.Value) FROM item AS t2 WHERE t1.item_id = t2.item_id GROUP BY t2.item_id ) AS Value FROM item AS t1 

Demo http://sqlfiddle.com/#!9/bbc20/18/0