Comma Separated values into rows in mysql

Question

SELECT DISTINCT jp.skills
FROM job_profile jp
UNION
SELECT js.skills
FROM job_seeker_profile js

The result:

|skills              |
|php                 |
|PHP,Jquery,MVC      | 
|java                |
|.net                |   
|Tally               | 
|php, mysql, yii     |
|css, html, bootstrap|
|javascript, json    |

but I need this as each item as row (each comma separated values as rows)

Expected result:

|skills   | 
|yii      |
|PHP      | 
|Jquery   |
|MVC      |
|.net     |   
|Tally    | 
|bootstrap|
|css      |
|html     |

Look here http://stackoverflow.com/questions/471914/can-you-split-explode-a-field-in-a-mysql-query — MrGapo, Jun 02 '16 at 07:57
check this mysql function if it address your requirement http://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_group-concat — Sohaib Farooqi, Jun 02 '16 at 07:57
Possible duplicate of [Can Mysql Split a column?](http://stackoverflow.com/questions/1096679/can-mysql-split-a-column) — Pred, Jun 02 '16 at 07:58
Ok I use this `SELECT DISTINCT SUBSTRING_INDEX(SUBSTRING_INDEX(jp.skills , ',', js.skills ), ',', -1) value FROM job_profile jp ,job_seeker_profile js WHERE js.skills <= 1 + (LENGTH(jp.skills) - LENGTH(REPLACE(js.skills , ',', ''))) ORDER BY value` But returns empty result :( — Jackhad, Jun 02 '16 at 08:04

Ranjit Singh · Answer 1 · 2016-06-02T08:59:43.047

0

Create a split function which will split the data on , then create row wise data

SELECT distinct  Split.fn.value('.', 'VARCHAR(100)') AS skills  
 FROM  
 (SELECT  CAST ('<a>' + REPLACE(skills, ',', '</a><a>') + '</a>' AS XML) AS Data  
  FROM  job_profile
 ) AS A CROSS APPLY Data.nodes ('/a') AS Split(fn);

Note: Update the inner query according to your need

Updated : In your case query will be

SELECT distinct  Split.fn.value('.', 'VARCHAR(100)') AS skills  
 FROM  
 (SELECT  CAST ('<a>' + REPLACE(jp.skills, ',', '</a><a>') + '</a>' AS XML) AS Data  
  FROM  job_profile jp
  UNION
    SELECT js.skills
    FROM job_seeker_profile js
 ) AS A CROSS APPLY Data.nodes ('/a') AS Split(fn);

edited Jun 02 '16 at 08:59

answered Jun 02 '16 at 08:04

Ranjit Singh

3,715
1
21
35

Confusing.. Is this query is Right: `SELECT DISTINCT SUBSTRING_INDEX(SUBSTRING_INDEX(jp.skills , ',', js.skills ), ',', -1) value FROM job_profile jp ,job_seeker_profile js WHERE js.skills <= 1 + (LENGTH(jp.skills) - LENGTH(REPLACE(js.skills , ',', ''))) ORDER BY value' – Jackhad Jun 02 '16 at 08:06
This query is also very confusing – Ranjit Singh Jun 02 '16 at 08:17
Please let me know what is the confusion about query shared in answer, will help you to understand that. – Ranjit Singh Jun 02 '16 at 08:18
Lookout my query in the Question ,im combining two tables and showing it in a row.So im bit confused about how to apply your query? – Jackhad Jun 02 '16 at 08:24
@Jackhad Updated in answer – Ranjit Singh Jun 02 '16 at 08:59

Comma Separated values into rows in mysql

1 Answers1