MySQL - Move Comma Separated Values to new Table Column

Question

I have a row in a MySQL column named tags.

In tags I have a series of keywords separated by commas. For this example lets use ice cream as the theme.. mmmmmmm.... Ice Cream :).

So in my tags row I have:

Vanilla, Strawberry, Coconut, Chocolate, Rocky Road, etc.

I also have a column in the same table named Ice Cream Shop. Each with just one shop name and an id.

So the entire record in the the table will have:

Shop ID: 1 Shop Name: Yum Yum Tags: Vanilla, Strawberry, Coconut, Chocolate, Rocky Road

Now here's the kicker.. in the table mentioned above there is more rows in the table which I need to keep where they are.

What I want to do is take the tags aspect and move it to a completely new table with only the info I want from the table.

So essentially I need to do this:

• Copy the following Data from the ID Row, Shop Row, and Tags row into a new table Title Ice Cream.

Then be able to take all tags within the tags row and put each tag in its own row with corresponding ID, etc.

So:

Table_Name 1

ID | Shop    | Tags
---------------------
1  | Yum Yum | Vanila, Strawberry, Chocolate, Coconut

to

New_Table (probably named tags)

ID | Tag        | Shop
------------------------------
1  | Vanila     | Yum Yum
2  | Strawberry | Yum Yum
3  | Chocolate  | Yum Yum
4  | Coconut    | Yum Yum

Is this possible? If so how can I go about doing this in MySQL?

Answer 1

Take a look at the discussion here. It's about writing a stored procedure with the same purpose as yours.

Answer 2

     SELECT shop,
     GROUP_CONCAT(DISTINCT tag
               ORDER BY tag SEPARATOR ',')
     FROM Table
     GROUP BY shop;