CODEWITHSUNDEEP
mysql
Krunal
Krunal

Reputation: 2203

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

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

Upvotes: 6

Views: 519

Answers (4)

carles
carles

Reputation: 360

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)");
  }
}
?>

Upvotes: 1

gnur
gnur

Reputation: 4733

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.

Upvotes: 2

btilly
btilly

Reputation: 46507

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.

Upvotes: 4

Mark Byers
Mark Byers

Reputation: 839114

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)

Upvotes: 4

Related Questions