如何使用Cross应用SQL中的单独行分开一个以上的逗号分隔列

Question

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.通过使用此选项查询，我会产生结果，直到电话列，但粘在第二列。 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

Answer 1

由于 - 根据您的描述 - 您使用交叉应用，并且您的查询已成功执行，因此这意味着您使用的是SQL Server DB，而不是我的SQL。您可以进行两个交叉应用以获得预期的结果。 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.我强烈建议将来为不同值创建单独的列。

Answer 2

好吧，我自己找到了解决方案。
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.