comma separated values in mysql in to multiple rows

Question

I'm ahaving the problem in mysql query,,I need mysql query for display the following output and also i need to take csv or excel or pdf report .

Table1:

id : nos
---------
1    12,13,14
2    14
3    14,12

id : values
------------
12   raja
13   rames
14   ravi

I want output like this:

id  values
---------------------
1   raja
1   rames
1   ravi
2   ravi
3   ravi
3   raja

You should consider to normalize your tables. Storing several values in one column is bad practice. — Marcus, Nov 11 '11 at 08:52
A similar question was asked yday by you - http://stackoverflow.com/questions/8075323/mysql-with-comma-separated-values - have you tried writing any queries? — Jan S, Nov 11 '11 at 08:57
@raja: Try to use the `FIND_IN_SET()` function: http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_find-in-set — ypercubeᵀᴹ, Nov 11 '11 at 09:39

score 1 · Answer 1 · answered Nov 11 '11 at 09:35

1

The following query will split out the list, but its not likely to perform well on a large table.

SELECT table1.id, table2.values
FROM table1
    JOIN table2 ON CONCAT(',', table1.nos, ',') LIKE CONCAT(',' table2.id, ',')

answered Nov 11 '11 at 09:35

a'r

score 0 · Answer 2 · answered Apr 17 '19 at 21:04

0

if you have nos1, nos2, nos3 ... ecc

SELECT a.id,a.nos
        b.id,b.values
FROM Table1 as a, Table2 as b
WHERE (a.nos1 = b.id) OR
   (a.nos2 = b.id) OR
   (a.nos3 = b.id)

answered Apr 17 '19 at 21:04

carmeloconny

score 0 · Answer 3 · answered Nov 11 '11 at 09:03

You need to save your data in table 1 in multiple records. for example data will be

id : nos
---------
1    12
1    13
1    14
2    14
3    14
3    12

Then you can use following query to get your result

select table1.id, table2.values from table1,table2 where table1.nos=table2.id