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

Question

我有一个大约有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   
----------------|-----------------|----------

我希望查询结果是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   
----------------|-----------------|----------

我该如何实现这一点？我正在使用SQL Server 2012。

Answer 1

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

然后：

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;

它使用SQL Server2016中的string_split()函数，但在早期版本中有很多类似的函数。

Answer 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   ;