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 |
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.