开发者

sql string cat as aggregate function: cat multiple records together

I have a table with columns: (project_id, name)

It's a list of people, each one with the project it belongs to. If a person is in two projects, it is duplicated.

I want to extract a table with columns: (project_id, people) where people is a string cat of the names of all person working on that project. The cat must be comma separated, like this:

12, john
12, mark
12, dave
14, luke

becomes

开发者_开发问答12, "john, mark, dave"
14, "luke"


you can do this with a simple query

SELECT project_id, GROUP_CONCAT(name) as people
FROM table
GROUP BY project_id

if you insist to have space after the comma:

SELECT project_id, GROUP_CONCAT(name SEPARATOR ", ") as people
FROM table
GROUP BY project_id


Group concat will help you in this case

Refer this , http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜