Write SQL Query to transform comma separated values into rows using Oracle, PostgreSQL, SQL Server, MySQL

 In our previous post we explored how to transform multiple rows to single rows.(Multiple rows to single row )

In this post we will transform the data from the format where the field contains multiple comma-separated values into individual rows, you can use a Common Table Expression (CTE) or a recursive query, depending on your SQL database capabilities. 

 Here’s how you can achieve this in standard SQL:

 #ORACLE

WITH CTE AS (

 SELECT 1 ID,'a,b,c' AS NAME FROM DUAL UNION ALL

SELECT 2 ID,'d,e' AS NAME FROM DUAL

)

SELECT DISTINCT ID,REGEXP_SUBSTR(NAME,'[^,]+',1,LEVEL)  FROM CTE 

CONNECT BY REGEXP_SUBSTR (NAME,'[^,]+', 1, LEVEL) IS NOT NULL ORDER BY 1;

 

#MYSQL

 

with recursive splitnames as (

select id, substring_index(name, ',', 1) as name,

substring(name, length(substring_index(name, ',', 1)) + 2) as rest

from 

(select 1 id,'a,b,c' as name from dual union select 2 id,'d,e' as name from dual) cte

 union all

 select id,

substring_index(rest, ',', 1), substring(rest, length(substring_index(rest, ',', 1)) + 2)

from splitnames where rest <> ''

)

select id,name from splitnames where  name <> '' order by 1;

 

#POSTGRESQL

with cte as (

 select 1 id,'a,b,c' as name union all

select 2 id,'d,e' as name 

)

 SELECT 

    id,

    unnest(string_to_array(name, ',')) AS name

FROM 

    cte;

 

#SQLSERVER

WITH SplitNames AS (

    SELECT id,TRIM(value) AS name

    FROM 

        (select 1 as id,'a,b,c' as name union all 

        select 2 as id,'d,e' as name 

        ) x

    CROSS APPLY STRING_SPLIT(name, ',')

)

SELECT id,name FROM SplitNames;