How to get all combinations of comma separated values in a T-SQL query

Question

I have table with three columns: column 2 and 3 contains comma-separated values.

   -col1----col2---col3--
   | 1  |  1,2,3  | 4,5 |
   ----------------------

What is the most efficient way to get a table of three columns that contains all the combinations of values of these three columns, like this:

1 | 1 | 4
1 | 2 | 4
1 | 3 | 4
1 | 1 | 5
1 | 2 | 5
1 | 3 | 5

Answer 1

Using query and nodes:

DECLARE @t TABLE (col1 VARCHAR(100), col2 VARCHAR(100), col3 VARCHAR(100)) 
INSERT @t VALUES ('1', '1,2,3', '4,5') 

;WITH cte AS 
(
    SELECT
        col1 = CAST('<x>' + REPLACE(col1, ',','</x><x>') + '</x>' AS XML),
        col2 = CAST('<x>' + REPLACE(col2, ',','</x><x>') + '</x>' AS XML),
        col3 = CAST('<x>' + REPLACE(col3, ',','</x><x>') + '</x>' AS XML)
    FROM @t 
) 
SELECT 
    col1.n.query('.[1]').value('.', 'int'),
    col2.n.query('.[1]').value('.', 'int'),
    col3.n.query('.[1]').value('.', 'int')
FROM 
    cte
CROSS APPLY col1.nodes('x') AS col1(n)
CROSS APPLY col2.nodes('x') AS col2(n)
CROSS APPLY col3.nodes('x') AS col3(n)

SQL Fiddle

Answer 2

Try this:

DECLARE @T1 TABLE (COL1 VARCHAR(25), COL2 VARCHAR(25), COL3 VARCHAR(25))

INSERT INTO @T1 (COL1,COL2,COL3)
VALUES ('1','1,2,3','4,5')

DECLARE @COL1 TABLE (VAL1 VARCHAR(25))
DECLARE @COL2 TABLE (VAL2 VARCHAR(25))
DECLARE @COL3 TABLE (VAL3 VARCHAR(25))

INSERT INTO @COL1 (VAL1)   
SELECT DISTINCT Split.a.value('.', 'VARCHAR(max)') AS String
FROM  (SELECT CAST ('<M>' + REPLACE(CAST(COL1 AS VARCHAR), ',', '</M><M>') + '</M>' AS XML) AS String  
       FROM @t1) AS A 
CROSS APPLY String.nodes ('/M') AS Split(a)

INSERT INTO @COL2 (VAL2)   
SELECT DISTINCT Split.a.value('.', 'VARCHAR(max)') AS String
FROM  (SELECT CAST ('<M>' + REPLACE(CAST(COL2 AS VARCHAR), ',', '</M><M>') + '</M>' AS XML) AS String  
       FROM @t1) AS A 
CROSS APPLY String.nodes ('/M') AS Split(a)

INSERT INTO @COL3 (VAL3)   
SELECT DISTINCT Split.a.value('.', 'VARCHAR(max)') AS String
FROM  (SELECT CAST ('<M>' + REPLACE(CAST(COL3 AS VARCHAR), ',', '</M><M>') + '</M>' AS XML) AS String  
       FROM @t1) AS A 
CROSS APPLY String.nodes ('/M') AS Split(a)

SELECT * 
FROM @COL1
CROSS APPLY @COL2
CROSS APPLY @COL3
ORDER BY VAL1,VAL2,VAL3