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;