CODEWITHSUNDEEP
mysql
ASD
ASD

Reputation: 4967

MySQL query to concatenate all the values in each row based on the common same id matching

Is there any way to concatenate the value in a field that matching with a common 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

Upvotes: 2

Views: 1436

Answers (4)

Sentry
Sentry

Reputation: 4113

GROUP_CONCAT

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

Upvotes: 1

mdma
mdma

Reputation: 57757

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

Upvotes: 3

Matti Virkkunen
Matti Virkkunen

Reputation: 65156

Use GROUP_CONCAT

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

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 453628

You need GROUP_CONCAT

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

Upvotes: 2

Related Questions