Home How to split more than one comma separated column as a separate row in SQL using CROSS APPLY

Questions

How to split more than one comma separated column as a separate row in SQL using CROSS APPLY

May 30, 2022

I have one table having following rows

Name	Phones	Courses
ABC	123, 456	HTML, Java
XYZ	321, 654	PHP, CSS

now I want to write a SELECT query to get these comma separated Phone and Courses as a separate row. By using this SELECT query I yield results till Phone column but stuck at 2nd column. Select query is

SELECT 
    Name, 
    value phone,
    courses
FROM 
    tblName 
    CROSS APPLY STRING_SPLIT(phones, ',');

This query shows me following results:

Name	Phones	Courses
ABC	123	HTML, Java
ABC	456	HTML, Java
XYZ	321	PHP, CSS
XYZ	654	PHP, CSS

Please help me to split Courses column as like Phones and want to yield following results:

Name	Phones	Courses
ABC	123	HTML
ABC	456	HTML
ABC	123	Java
ABC	456	Java
XYZ	321	PHP
XYZ	654	PHP
XYZ	321	CSS
XYZ	654	CSS

>Solution :

Since – according to your description – you used CROSS APPLY and your query was successfully executed, this means you are using a SQL Server DB, not MY SQL. You can do two CROSS APPLY to get your expected result. This will produce exactly the outcome you have shown in your question:

SELECT name, phone, value courses FROM
(SELECT name, value phone, courses 
FROM tblName CROSS APPLY STRING_SPLIT(phones, ',')) x
CROSS APPLY STRING_SPLIT(courses, ',')
ORDER BY name, courses, phone;

You can verify this here: db<>fiddle

But this is very risky and you really should avoid such comma-separated contents in one column. I highly recommend to create separate columns for the different values in future.