SQL Recursive CTE unexpectedly returns alternating sets

Question

I am trying to get the use recursive CTE to repeat the same pattern over and over, resetting when "Scenario" increases in value. RowNumber repeats 1-21 (as desired), but whenever "Scenario" is an even number, there are too few items in the "Vals" column to feed into "Value". I can't figure out which part of the code is causing me to be 1 short for only even Scenarios.

Below are the results of the code I'm using at the bottom.

Scenario    RowNumber   Value   Vals
1   1   A   A,A,A,A,A,A,A,A,A,A,A,A,A,A,A,A,A,A,B,C
1   2   A   A,A,A,A,A,A,A,A,A,A,A,A,A,A,A,A,A,B,C
1   3   A   A,A,A,A,A,A,A,A,A,A,A,A,A,A,A,A,B,C
1   4   A   A,A,A,A,A,A,A,A,A,A,A,A,A,A,A,B,C
1   5   A   A,A,A,A,A,A,A,A,A,A,A,A,A,A,B,C
1   6   A   A,A,A,A,A,A,A,A,A,A,A,A,A,B,C
1   7   A   A,A,A,A,A,A,A,A,A,A,A,A,B,C
1   8   A   A,A,A,A,A,A,A,A,A,A,A,B,C
1   9   A   A,A,A,A,A,A,A,A,A,A,B,C
1   10  A   A,A,A,A,A,A,A,A,A,B,C
1   11  A   A,A,A,A,A,A,A,A,B,C
1   12  A   A,A,A,A,A,A,A,B,C
1   13  A   A,A,A,A,A,A,B,C
1   14  A   A,A,A,A,A,B,C
1   15  A   A,A,A,A,B,C
1   16  A   A,A,A,B,C
1   17  A   A,A,B,C
1   18  A   A,B,C
1   19  A   B,C
1   20  B   C
1   21  C   
2   1   A   A,A,A,A,A,A,A,A,A,A,A,A,A,A,A,A,B,B,C
2   2   A   A,A,A,A,A,A,A,A,A,A,A,A,A,A,A,B,B,C
2   3   A   A,A,A,A,A,A,A,A,A,A,A,A,A,A,B,B,C
2   4   A   A,A,A,A,A,A,A,A,A,A,A,A,A,B,B,C
2   5   A   A,A,A,A,A,A,A,A,A,A,A,A,B,B,C
2   6   A   A,A,A,A,A,A,A,A,A,A,A,B,B,C
2   7   A   A,A,A,A,A,A,A,A,A,A,B,B,C
2   8   A   A,A,A,A,A,A,A,A,A,B,B,C
2   9   A   A,A,A,A,A,A,A,A,B,B,C
2   10  A   A,A,A,A,A,A,A,B,B,C
2   11  A   A,A,A,A,A,A,B,B,C
2   12  A   A,A,A,A,A,B,B,C
2   13  A   A,A,A,A,B,B,C
2   14  A   A,A,A,B,B,C
2   15  A   A,A,B,B,C
2   16  A   A,B,B,C
2   17  A   B,B,C
2   18  B   B,C
2   19  B   C
2   20  C   
2   21  A   A,A,A,A,A,A,A,A,A,A,A,A,A,A,A,A,A,B,B,C
3   1   A   A,A,A,A,A,A,A,A,A,A,A,A,A,A,A,A,A,B,C,C
3   2   A   A,A,A,A,A,A,A,A,A,A,A,A,A,A,A,A,B,C,C
3   3   A   A,A,A,A,A,A,A,A,A,A,A,A,A,A,A,B,C,C
3   4   A   A,A,A,A,A,A,A,A,A,A,A,A,A,A,B,C,C
3   5   A   A,A,A,A,A,A,A,A,A,A,A,A,A,B,C,C
3   6   A   A,A,A,A,A,A,A,A,A,A,A,A,B,C,C
3   7   A   A,A,A,A,A,A,A,A,A,A,A,B,C,C
3   8   A   A,A,A,A,A,A,A,A,A,A,B,C,C
3   9   A   A,A,A,A,A,A,A,A,A,B,C,C
3   10  A   A,A,A,A,A,A,A,A,B,C,C
3   11  A   A,A,A,A,A,A,A,B,C,C
3   12  A   A,A,A,A,A,A,B,C,C
3   13  A   A,A,A,A,A,B,C,C
3   14  A   A,A,A,A,B,C,C
3   15  A   A,A,A,B,C,C
3   16  A   A,A,B,C,C
3   17  A   A,B,C,C
3   18  A   B,C,C
3   19  B   C,C
3   20  C   C
3   21  C   
4   1   A   A,A,A,A,A,A,A,A,A,A,A,A,A,A,A,B,B,B,C
4   2   A   A,A,A,A,A,A,A,A,A,A,A,A,A,A,B,B,B,C
4   3   A   A,A,A,A,A,A,A,A,A,A,A,A,A,B,B,B,C
4   4   A   A,A,A,A,A,A,A,A,A,A,A,A,B,B,B,C
4   5   A   A,A,A,A,A,A,A,A,A,A,A,B,B,B,C
4   6   A   A,A,A,A,A,A,A,A,A,A,B,B,B,C
4   7   A   A,A,A,A,A,A,A,A,A,B,B,B,C
4   8   A   A,A,A,A,A,A,A,A,B,B,B,C
4   9   A   A,A,A,A,A,A,A,B,B,B,C
4   10  A   A,A,A,A,A,A,B,B,B,C
4   11  A   A,A,A,A,A,B,B,B,C
4   12  A   A,A,A,A,B,B,B,C
4   13  A   A,A,A,B,B,B,C
4   14  A   A,A,B,B,B,C
4   15  A   A,B,B,B,C
4   16  A   B,B,B,C
4   17  B   B,B,C
4   18  B   B,C
4   19  B   C
4   20  C

This is the code I used to generate the above sample. Where am I going wrong?

CREATE TABLE #temp3
        (
        Scenario INT
        ,Vals VARCHAR(64)
        ,LEN INT
        )
        ;
        WITH vals AS 
            (
            SELECT 
                v.*
            FROM 
                (VALUES ('A'), ('B'), ('C')) v(x)
            ),
        CTE AS 
                (
                SELECT CAST('A' AS VARCHAR(MAX)) AS STR, 0 AS LEN 
                UNION ALL
                SELECT (CTE.STR + ',' + vals.x), CTE.LEN + 1
                FROM 
                    CTE 
                JOIN vals
                    ON vals.x >= RIGHT(CTE.STR, 1)
                WHERE CTE.LEN < 19
                )
        INSERT INTO #temp3
        SELECT 
            ROW_NUMBER() OVER(ORDER BY STR + ',C') AS Scenario
            ,STR + ',C' AS Vals
            ,LEN
        FROM 
            CTE
        WHERE 
            STR + 'C' LIKE '%B%'
            AND LEN = 19
            ;

        -- Split strings created above into individual characters

        WITH cte(Scenario, Value, Vals) AS 
        (
            SELECT 
                Scenario 
                ,CAST(LEFT(Vals, CHARINDEX(',',Vals+',')-1) AS VARCHAR(10)) AS Value
                ,STUFF(Vals, 1, CHARINDEX(',',Vals+','), '') AS Vals
            FROM #temp3
            UNION ALL
            SELECT 
                Scenario
                ,CAST(LEFT(Vals, CHARINDEX(',',Vals+',')-1)  AS VARCHAR(10))
                ,STUFF(Vals, 1, CHARINDEX(',',Vals+','), '') 
            FROM cte
            WHERE Vals > ''
         )

       SELECT 
           Scenario
           ,ROW_NUMBER() OVER (PARTITION BY Scenario ORDER BY Scenario) RowNumber
           ,Value
           ,Vals
       FROM cte t

score 0 · Answer 1 · answered May 22 '17 at 23:11

I'm not exactly sure what the problem you are describing is, but the ROW_NUMBER() should use an ORDER BY clause that completely orders the rows in each partition.

When you use "PARTITION BY Scenario ORDER BY Scenario" the order in which the ROW_NUMBER() values are assigned is undefined. Try something like

WITH cte(Scenario,  depth, Value, Vals) AS 
        (
            SELECT 
                Scenario, 0 depth
                ,CAST(LEFT(Vals, CHARINDEX(',',Vals+',')-1) AS VARCHAR(10)) AS Value
                ,STUFF(Vals, 1, CHARINDEX(',',Vals+','), '') AS Vals
            FROM #temp3
            UNION ALL
            SELECT 
                Scenario,  depth+1
                ,CAST(LEFT(Vals, CHARINDEX(',',Vals+',')-1)  AS VARCHAR(10))
                ,STUFF(Vals, 1, CHARINDEX(',',Vals+','), '') 
            FROM cte
            WHERE Vals > ''
         )

       SELECT 
           Scenario 
           ,depth
           ,ROW_NUMBER() OVER (PARTITION BY Scenario ORDER BY depth ) RowNumber
           ,Value
           ,Vals
       FROM cte t

SQL Recursive CTE unexpectedly returns alternating sets

1 Answers1