How to create all possible combinations with splitted items in SQL?

I have data as in below delimited format

data - '1A,2B,2A'

expected output – is the combination with each element and the element itself

OrderId combination
    1   1A,2B
    2   1A,2A
    3   2B,1A
    4   2B,2A
    5   2A,1A
    6   2A,2B
    7   1A
    8   2B
    9   2A 

I am trying by splitting the data into different items as below

    DECLARE @data VARCHAR(100);    
    SET @data = '1A,2B,2A   

    DECLARE @Temp TABLE
            (
             OrderId INT IDENTITY,             
             Combo VARCHAR(10)
            );

--insert  data to a table
        INSERT  INTO @Temp
               SELECT 
               Split.temp.value('.', 'NVARCHAR(MAX)') combination
              FROM    ( SELECT    CAST('<X>' + REPLACE(@data, ',',
                                                      '</X><X>') + '</X>' AS XML) AS String
                      ) AS temp
                      CROSS APPLY String.nodes('/X') AS Split ( temp );

any idea how we can create combination with splitted items?

Thanks!

Answer

You have a couple of mistakes in your SQL. @data had no closing “‘”, and you have the wrong separator in your split function (‘|’ instead of ‘,’).

Once you have the split values, you can simply get the combinations by a self cross join, where the split data doesn’t match. Finally get your orderid based on length and value. Something like this should get the results you need, without the need for a cursor.

DECLARE @data VARCHAR(100);    
SET @data = '1A,2B,2A,3C'   

DECLARE @temp TABLE (splitdata varchar(5))
INSERT INTO @temp(splitdata)
SELECT Split.temp.value('.', 'NVARCHAR(MAX)') combination
FROM
(
    SELECT CAST('<X>'+REPLACE(@data, ',', '</X><X>')+'</X>' AS XML) AS String
) AS temp
CROSS APPLY String.nodes('/X') AS Split(temp);


INSERT INTO @temp(splitdata)
SELECT t1.splitdata + ',' + t2.splitdata FROM @temp t1
CROSS JOIN @temp t2 WHERE t1.splitdata <> t2.splitdata

DECLARE @result TABLE (orderid int identity(1, 1), combo varchar(10))

INSERT INTO @result (combo)
SELECT splitdata 
FROM @temp
ORDER BY (LEN(splitdata)) DESC, splitdata

SELECT * from @result