comma separated value separation

I have one table with the following kinds of records in it. I have Salesman and associated account numbers (comma separated).

开发者_StackOverflow中文版+----------+----------+
| Salesman | Acct     |
+----------+----------+
| Ron      | 1,2,3,4  |
| Kin      | 6,7,8    |
| Joe      | 10,23,45 |

I am looking for output some thing like,

+----------+----------+
| Salesman | Acct     |
+----------+----------+
| Ron      | 1        |
| Ron      | 2        |
| Ron      | 3        |

Please help me write the query that can give me above result.

Thank you for your help.

---

I assume that you need this just as a one-off operation in order to restructure your schema into something usable.

Do you have another table which lists all the possible account numbers? If you do then you can do this:

SELECT salesman, a.Acct
FROM account AS a
JOIN salesman_account AS sa
ON FIND_IN_SET(a.Acct, sa.Acct)

---

You can do this with a stored procedure. See http://kedar.nitty-witty.com/blog/mysql-stored-procedure-split-delimited-string-into-rows for an example that looks like it solves your problem.

---

If it is a one off operation then it probably is easier to use any supporting language to do the processing. Python or PHP is very fast in developing such kind of scripts.

---

Well it's a very hard thing to do.

I'll assume you're normalizing your database. If not, you really should.

If I were you, I'd write a script to normalize the table. Next is a sample in php (it may contain errors):

<?php
$res = mysql_query('SELECT Salesman, Acct FROM table');
while ($obj = mysql_fetch_object($res)) {
  $values = explode(',', $obj->Acct);
  foreach ($values as $value) {
    mysql_query("INSERT INTO new_table (Salesman, Acct) VALUES ('$obj->Salesman', $value)");
  }
}
?>