MYSql query to concanate all the values in each row based on the comman same id matching.

Question

Is there any way to concatenate the value in a field that matching with a comman field say userid.

For eg if this is the table,

UserID| Field1  |  Field2

---

1        | aaa    |   zzz
1        | bbb    |   yyy
1        | ccc    |   xxx

i want it as a single row like--
UserID | Field1  |  Field2

---

1        | aaa,bbb,ccc,    |   zzz,yyy,xxx

Thanks

Answer 1

You need GROUP_CONCAT

SELECT UserId GROUP_CONCAT(Field1) AS A, GROUP_CONCAT(Field2) AS B
FROM TBL
GROUP BY  UserId

Answer 2

Use GROUP_CONCAT

SELECT UserID, GROUP_CONCAT(Field1), GROUP_CONCAT(Field2)
FROM table
GROUP BY UserID

Answer 3

The mysql GROUP_CONCAT aggregate function does this nicely.

SELECT UserID, GROUP_CONCAT(field1), GROUP_CONCAT(field2)
FROM Users
GROUP BY UserID

To get the order you require, you can specify the order to GROUP_CONCAT

SELECT UserID, GROUP_CONCAT(field1 ORDER BY field1,field2), GROUP_CONCAT(field2 ORDER BY field1, field2)
FROM Users
GROUP BY UserID

Answer 4

GROUP_CONCAT

SELECT UserID, GROUP_CONCAT(Field1), GROUP_CONCAT(Field2)
FROM table
GROUP BY UserID