merge the value in a table

blackhorse

say the table has two columns, name and group, the data like this

name group

steve group1
jim group1
jen group2

I want to run a query on this table, and then turn the data in this table to a new table like

name group

steve, jim group1
jen group2

So the value of column 'group' will be unique in the new table, and the value of name will be joined for the same 'group'.

Any advices?

I could read the data from old table to php and merged the name value for the same group and then insert the data back to second table. But are there any queries or procedures that I can just run directly in the mysql for this process?

Thanks!

bradgrafelman

Is there a particular reason you want to do this? There is certainly a way you could do it all in one query, but I'm not sure why you would want to; doing so would basically un-normalize your database.

blackhorse

The reason is that I am reading this table from a 3rd party database. And as you can see, there is no key I can use in the first case. But if I convert it to the second case, then I can use the group as a key.

bradgrafelman

Well there certainly appears to be a (primary) key in the first case, either on the name column or the name and group columns.

I'm still confused as to how you're using this data. What does the 3rd party database look like, and what are you trying to do with that data?

Sorry if it seems like I'm dragging my feet here, but as I said before it seems like you're wanting to un-normalize your database, and it's hard enough to get people to create normalized schema (which often makes data manipulation much easier - now, in the future, or both) as it is.

blackhorse

The 3rd party table is like this

name group

steve group1
jim group1
jen group2
steve group2
sam, jen group3

The name column could be one name, or comma separated several names

steve
jim
jen
steve
sam, jen

group column could be

group1
group1
group2
group2
group3

So you can see neither name nor group is unique.

What I want is merge the values of name for the same group,

So it could look like this

name group

steve, jim group1
jen, steve group2
sam, jen group3

so the group will be unique, and then I would use group as the key.

johanafm

I'd still recommend normalizing it completely.

id   name
1  steve
2  jim

group  person
1      1
1      2
2      1

Weedpacket

There's no point fighting relational database theory unless and until you have a real performance issue.

blackhorse wrote:
so the group will be unique, and then I would use group as the key.

Is that so crucial? You could use (name,group) as the primary key; or you could create a plain autoincrement field and use that; or you can go without a primary key at all. Whichever way you do it you'd probably want to index name and group; if you do it the way you're proposing you won't be able to create a meaningful index on name (I guess that you might want to search for all groups for a given name).