sqlsql-serverselectcross-apply

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


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.