Separate the comma values in single row in sql query

Question

I have this table structure

 id     flim_name     flim_actor_name
 1      Diwalie       Shah Rukh Khan,Kajol,Varun Dhawan
 2      Mersal        vijay, samantha,kajol

I need to convert table in to this structure

id     flim_name     Actor_name       Heroin_name  director_name
1       Diwalie      Shah Rukh Khan     Kajol       Varun Dhawan
2       Mersal       vijay              samantha       kajol

I need the sql query to solve this

Try this: https://stackoverflow.com/questions/10581772/how-to-split-a-comma-separated-value-to-columns EDIT: Actually, this is a MySQL version which is more suited to your case: https://stackoverflow.com/questions/1096679/can-mysql-split-a-column — itsmewiththeface, Aug 21 '17 at 03:31
Are you sure about the tags? mySQL, SQL and Oracle. Which database are you using? Answer may or may not depend on that but still tagging it appropriately will make it easy for people to help you. — PM., Aug 21 '17 at 03:38
Select id, flim_name, substring_index(substring_index ( `flim_actor_name`,',',1 ), ',',-1)as Hero, substring_index(substring_index ( `flim_actor_name`,',',2 ), ',', -1)as Heroin, substring_index(substring_index(`flim_actor_name`,",",3),',',-1) as director_name from flim_management_table — vallavan, Aug 21 '17 at 08:01
This the query whatever you need put after as that is what I am doing. — vallavan, Aug 21 '17 at 08:01

score 0 · Answer 1 · answered Aug 21 '17 at 03:38

0

Use implode for this

ex.

$arr = array(Shah Rukh Khan,Kajol,Varun Dhawan);
echo implode(" ",$arr);
echo implode(" ",$arr)."<br>";
echo implode("+",$arr)."<br>";
echo implode("-",$arr)."<br>"; 
echo implode("X",$arr);

Ouput:

Shah Rukh Khan Kajol Varun Dhawan
Shah Rukh Khan+Kajol+Varun Dhawan
Shah Rukh Khan-Kajol-Varun Dhawan
Shah Rukh KhanXKajolXVarun Dhawan

answered Aug 21 '17 at 03:38

Barclick Flores Velasquez

673
6
12

I got solution I posted it below,,,Thanks for all,, – vallavan Aug 21 '17 at 03:45

score 0 · Answer 2 · answered Aug 21 '17 at 03:43

Answer for the question is

Select id, flim_name,
substring_index(substring_index ( `flim_actor_name`,',',1 ), ',',-1)as Hero,
substring_index(substring_index ( `flim_actor_name`,',',2 ), ',', -1)as Heroin,  
substring_index(substring_index(`flim_actor_name`,",",3),',',-1) as director_name   
from flim_management_table

Table structure:

 id      flim_name     Hero             Heroin       director_name
  1       Diwalie      Shah Rukh Khan     Kajol       Varun Dhawan
  2       Mersal       vijay              samantha       kajol

I got a solution..I post it..thanks for all – vallavan Aug 21 '17 at 03:45 — vallavan, Aug 21 '17 at 03:45

Separate the comma values in single row in sql query

2 Answers2