I have one table having following rows

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:

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

CodePudding user response：

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.

CodePudding user response：

Well I found the solution myself. here is the working query:

SELECT 
    t1.name,
    t1.phone,
    value course
FROM
    (SELECT 
        first_name, 
        value phone,
        courses
    FROM 
        tblName 
        CROSS APPLY STRING_SPLIT(phones, ',')) t1
    CROSS APPLY STRING_SPLIT(t1.courses, ',');  

Thanks everyone.

CodePudding user response：

In MySQL there is no CROSS APPLY, on MySQL 8 you can use lateral join. First to get started, we’ll need a table that contains numbers at least as big as the length of our longest comma-separated list.

create temporary table numbers as (
  select 1 as n
  union select 2 as n
  union select 3 as n
  union select 4 as n
  union select 5 as n
  union select 6 as n
  union select 7 as n
  union select 8 as n
);

Then use:

select 
  Name, 
  substring_index( substring_index(Phones, ',', n),',', -1) as Phones,
  substring_index( substring_index(Courses, ',', n),',', -1) as Courses
from tbl
join numbers
  on char_length(Phones) 
    - char_length(replace(Phones, ',', '')) 
    >= n - 1

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=5dbf18633f9d99c725d7dbb928c3c478