SQL Server: How to CROSS JOIN between 2 Columns of the same table? ( Each Column contains Delimited Values)

Question

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

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.

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.

Answer 1

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

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.

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   ;