Hafiz K Hafiz K - 1 year ago 58
MySQL Question

MySQL convert comma separated result into Individual Columns

Im not sure whether my questions is right or not. I've been searching for the same term but seems none is near the result, I've table as below image:table img
Whats the suitable ways to get the result of 'Interested_in' column in individual column for each values with the result of "yes" & "no", which is stored as comma separated values?

I've tried below query but no luck!

SELECT SUBSTRING_INDEX(intereste_in,',',1) AS col1,
SUBSTRING_INDEX(SUBSTRING_INDEX(intereste_in,',',2),',',-1) AS col2,
SUBSTRING_INDEX(SUBSTRING_INDEX(intereste_in,',',3),',',-1) AS col3,
SUBSTRING_INDEX(SUBSTRING_INDEX(intereste_in,',',4),',',-1) AS col4,
SUBSTRING_INDEX(SUBSTRING_INDEX(intereste_in,',',5),',',-1) AS col5,
SUBSTRING_INDEX(SUBSTRING_INDEX(intereste_in,',',6),',',-1) AS col6,
SUBSTRING_INDEX(SUBSTRING_INDEX(intereste_in,',',7),',',-1) AS col7,
SUBSTRING_INDEX(SUBSTRING_INDEX(intereste_in,',',8),',',-1) AS col8,
SUBSTRING_INDEX(SUBSTRING_INDEX(intereste_in,',',9),',',-1) AS col9,
SUBSTRING_INDEX(intereste_in,',',-1) AS col10
FROM(SELECT unifying_column, GROUP_CONCAT(DISTINCT interested_in LIMIT 10) AS intereste_in FROM tblName_test
GROUP BY unifying_column) AS subtable;


Answer Source

So maybe something like:

SELECT CASE WHEN t.Inserted_In LIKE '%Shopping%' THEN 'Yes' ELSE 'No' END as Shooping,
       CASE WHEN t.Inserted_In LIKE '%Beauty%' THEN 'Yes' ELSE 'No' END as Beauty,
       CASE WHEN t.Inserted_In LIKE '%Toys%' THEN 'Yes' ELSE 'No' END as Toys,
FROM ...