SQL Server:如何在同一表的两列之间交叉连接?(每个列包含带分隔值)

I have a table with around 3000 rows. It looks like the one below.

我有一张大约3000行的桌子。它看起来像下面这个。

Delimited_Col_1 | Delimited_Col_2 | Date
----------------|-----------------|----------
    a           |    x1,x2        | Date-1   
    b,c         |    y1,y2,y3     | Date-2   
    d,e,f       |    z1,z2        | Date-3   
----------------|-----------------|----------

I want the query result to be a UNION of each row's CROSS JOIN between Delimited_Col_1 and Delimited_Col_2 CSVs... like below.

我希望查询结果是Delimited_Col_1和Delimited_Col_2 csv之间每一行的交叉连接的联合。像下面。

Resultant_Col_1 | Resultant_Col_2 | Date
----------------|-----------------|----------
    a           |    x1           | Date-1   
    a           |    x2           | Date-1   
----------------|-----------------|----------
    b           |    y1           | Date-2   
    b           |    y2           | Date-2   
    b           |    y3           | Date-2   
    c           |    y1           | Date-2   
    c           |    y2           | Date-2   
    c           |    y3           | Date-2   
----------------|-----------------|----------
    d           |    z1           | Date-3   
    d           |    z2           | Date-3   
    e           |    z1           | Date-3   
    e           |    z2           | Date-3   
    f           |    z1           | Date-3   
    f           |    z2           | Date-3   
----------------|-----------------|----------

How do I achieve this? I am using SQL Server 2012.

我该如何做到这一点?我正在使用SQL Server 2012。

2 个解决方案

#1

Search the web for a string split function (there are lots of examples).

在web上搜索字符串分割函数(有很多例子)。

Then:

然后:

select t.date, s1.value, s2.value
from t cross apply
     string_split(t.Delimited_Col_1) s1 cross apply
     string_split(t.Delimited_Col_2) s2;

This uses the string_split() function from SQL Server 2016, but similar functions abound for earlier versions.

这使用了来自SQL Server 2016的string_split()函数，但是在早期版本中有很多类似的函数。

#2

SELECT
  result.Col1  AS Resultant_Col_1 ,
  result.Col2  AS Resultant_Col_2 ,
result.[Date]
FROM
(

    SELECT
    Splitb.b.value('.', 'NVARCHAR(MAX)') Col1,
    Splita.a.value('.', 'NVARCHAR(MAX)') Col2,
    b.[Date]
    FROM
    (
        SELECT CAST('<X>'+REPLACE( Delimited_Col_2, ',', '</X><X>')+'</X>' AS XML) AS Col1,
              CAST('<Y>'+REPLACE( Delimited_Col_1, ',', '</Y><Y>')+'</Y>' AS XML) AS Col2,
              [Date]
      FROM  Table1
    ) AS b
    CROSS APPLY Col1.nodes('/X') AS Splita(a)
    CROSS APPLY Col2.nodes('/Y') AS Splitb(b)
 ) AS result
 ORDER BY result.Col1  ,
result.Col2   ;

#1