Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Creating permutation via recursive CTE in SQL server?

Looking at :

;WITH cte AS(
    SELECT 1 AS x UNION
    SELECT 2 AS x UNION     
    SELECT 3 AS x   
)

I can create permutation table for all 3 values :

SELECT T1.x , y=T2.x , z=t3.x
FROM cte T1
JOIN cte T2
ON T1.x != T2.x
JOIN cte T3
ON T2.x != T3.x AND T1.x != T3.x

This uses the power of SQL's cartesian product plus eliminating equal values.

http://i.imgur.com/uJUPtVH.png

OK.

But is it possible to enhance this recursive pseudo CTE :

;WITH cte AS(
    SELECT 1 AS x ,  2 AS y , 3 AS z   
    UNION ALL 
    ...
)

SELECT * FROM cte

enter image description here

So that it will yield same result as :

enter image description here

NB there are other solutions in SO that uses recursive CTE , but it is not spread to columns , but string representation of the permutations

like image 610
Royi Namir Avatar asked Jul 22 '15 06:07

Royi Namir


People also ask

How recursive CTE works in SQL Server?

A recursive CTE references itself. It returns the result subset, then it repeatedly (recursively) references itself, and stops when it returns all the results. FROM cte_name; Again, at the beginning of your CTE is the WITH clause.

What is the difference between CTE and recursive CTE?

If a query defines a CTE with a particular name, the CTE takes precedence over tables, etc. A CTE can be recursive or non-recursive. A recursive CTE is a CTE that references itself. A recursive CTE can join a table to itself as many times as necessary to process hierarchical data in the table.

What are the three main parts of the recursive CTE?

In general, a recursive CTE has three parts: An initial query that returns the base result set of the CTE. The initial query is called an anchor member. A recursive query that references the common table expression, therefore, it is called the recursive member.

How do I limit recursion on CTE?

There is no way to perform a recursion more than 32767, if you increase the value of MAXRECURSION more than 32767, as you will get an error. You can define the maximum number of recursions for CTE, using the MAXRECURSION option. Set the value of MAXRECURSION to 0, if you don't know the exact numbers of recursions.


1 Answers

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
like image 90
Nick Jacobsen Avatar answered Sep 20 '22 05:09

Nick Jacobsen