comma separated value separation

Question

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

+----------+----------+
| 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.

Answer 1

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)