Creating permutation via recursive CTE in SQL server?

I tried to do the lot in a CTE.

However trying to "redefine" a rowset dynamically is a little tricky. While the task is relatively easy using dynamic SQL doing it without poses some issues.

While this answer may not be the most efficient or straight forward, or even correct in the sense that it's not all CTE it may give others a basis to work from.

To best understand my approach read the comments, but it might be worthwhile looking at each CTE expression in turn with by altering the bit of code below in the main block, with commenting out the section below it.

SELECT * FROM <CTE NAME>

Good luck.

IF OBJECT_ID('tempdb..#cteSchema') IS NOT NULL
    DROP Table #cteSchema
GO

-- BASE CTE
;WITH cte AS( SELECT 1 AS x, 2 AS y, 3 AS z),

    -- So we know what columns we have from the CTE we extract it to XML
    Xml_Schema AS ( SELECT CONVERT(XML,(SELECT * FROM cte FOR XML PATH(''))) AS MySchema ),

    -- Next we need to get a list of the columns from the CTE, by querying the XML, getting the values and assigning a num to the column
    MyColumns AS (SELECT D.ROWS.value('fn:local-name(.)','SYSNAME') AS ColumnName,
                        D.ROWS.value('.','SYSNAME') as Value,
                        ROW_NUMBER() OVER (ORDER BY D.ROWS.value('fn:local-name(.)','SYSNAME')) AS Num
                    FROM Xml_Schema
                        CROSS APPLY Xml_Schema.MySchema.nodes('/*') AS D(ROWS) ),
    -- How many columns we have in the CTE, used a coupld of times below
    ColumnStats AS (SELECT MAX(NUM) AS ColumnCount FROM MyColumns),

    -- create a cartesian product of the column names and values, so now we get each column with it's possible values,
    -- so {x=1, x =2, x=3, y=1, y=2, y=3, z=1, z=2, z=3} -- you get the idea.
    PossibleValues AS (SELECT MyC.ColumnName, MyC.Num AS ColumnNum, MyColumns.Value, MyColumns.Num, 
                ROW_NUMBER() OVER (ORDER BY MyC.ColumnName, MyColumns.Value, MyColumns.Num ) AS ID
                FROM MyColumns 
                    CROSS APPLY MyColumns MyC
                ),

    -- Now we have the possibly values of each "column" we now have to concat the values together using this recursive CTE.
    AllRawXmlRows AS (SELECT CONVERT(VARCHAR(MAX),'<'+ISNULL((SELECT ColumnName FROM MyColumns WHERE MyColumns.Num = 1),'')+'>'+Value) as ConcatedValue, Value,ID, Counterer = 1  FROM PossibleValues
                UNION ALL
            SELECT CONVERT(VARCHAR(MAX),CONVERT(VARCHAR(MAX), AllRawXmlRows.ConcatedValue)+'</'+(SELECT ColumnName FROM MyColumns WHERE MyColumns.Num = Counterer)+'><'+(SELECT ColumnName FROM MyColumns WHERE MyColumns.Num = Counterer+1)+'>'+CONVERT(VARCHAR(MAX),PossibleValues.Value))  AS ConcatedValue, PossibleValues.Value, PossibleValues.ID,
            Counterer = Counterer+1
            FROM AllRawXmlRows
                INNER JOIN PossibleValues  ON AllRawXmlRows.ConcatedValue NOT LIKE '%'+PossibleValues.Value+'%' -- I hate this, there has to be a better way of making sure we don't duplicate values.... 
                AND AllRawXmlRows.ID <> PossibleValues.ID
                AND Counterer < (SELECT ColumnStats.ColumnCount FROM ColumnStats)
                ),

    -- The above made a list but was missing the final closing XML element. so we add it.
    -- we also restict the list to the items that contain all columns, the section above builds it up over many columns
    XmlRows AS (SELECT DISTINCT 
                ConcatedValue +'</'+(SELECT ColumnName FROM MyColumns WHERE MyColumns.Num = Counterer)+'>' 
                AS ConcatedValue
            FROM AllRawXmlRows WHERE Counterer = (SELECT ColumnStats.ColumnCount FROM ColumnStats)
                    ),              
    -- Wrap the output in row and table tags to create the final XML
    FinalXML AS (SELECT (SELECT CONVERT(XML,(SELECT CONVERT(XML,ConcatedValue) FROM XmlRows FOR XML PATH('row'))) FOR XML PATH('table') )as XMLData),

    -- Prepare a CTE that represents the structure of the original CTE with 
    DataTable AS (SELECT cte.*, XmlData
                        FROM FinalXML, cte)
--SELECT * FROM <CTE NAME>
    -- GETS destination columns with XML data.
SELECT * 
    INTO #cteSchema
FROM DataTable


DECLARE @XML VARCHAR(MAX) ='';
SELECT @Xml = XMLData FROM #cteSchema --Extract XML Data from the

ALTER TABLE #cteSchema DROP Column XMLData -- Removes the superflous column
DECLARE @h INT
EXECUTE sp_xml_preparedocument @h OUTPUT, @XML
    SELECT * 
        FROM OPENXML(@h, '/table/row', 2) 
            WITH #cteSchema -- just use the #cteSchema to define the structure of the xml that has been constructed

EXECUTE sp_xml_removedocument @h