Creating all possible combinations of a schedule using SQL Query

With a fixed number of qualifications, the answer is relatively simple - the option CROSS JOIN

from the previous answers will work just fine.

However, if the number of qualifications is unknown or may change in the future, the hardcodes of the four op's CROSS JOIN

will not work. In this case, the answer becomes more complex.

For a small number of lines, you can use a variation on this answer in the DBA that uses the powers of two and bit-comparisons to generate combinations. However, this will be limited to a very small number of lines.

For more lines, you can use the function to generate each combination of "M" numbers from the "N" lines. Then you can concatenate this value with the value ROW_NUMBER

calculated from the original data to get the original string.

The function for generating combinations can be written in TSQL, but it would be wiser to use SQLCLR if possible:

[SqlFunction(
    DataAccess = DataAccessKind.None,
    SystemDataAccess = SystemDataAccessKind.None,
    IsDeterministic = true,
    IsPrecise = true,
    FillRowMethodName = "FillRow",
    TableDefinition = "CombinationId bigint, Value int"
)]
public static IEnumerable Combinations(SqlInt32 TotalCount, SqlInt32 ItemsToPick)
{
    if (TotalCount.IsNull || ItemsToPick.IsNull) yield break;

    int totalCount = TotalCount.Value;
    int itemsToPick = ItemsToPick.Value;
    if (0 >= totalCount || 0 >= itemsToPick) yield break;

    long combinationId = 1;
    var result = new int[itemsToPick];
    var stack = new Stack<int>();
    stack.Push(0);

    while (stack.Count > 0)
    {
        int index = stack.Count - 1;
        int value = stack.Pop();

        while (value < totalCount)
        {
            result[index++] = value++;
            stack.Push(value);

            if (index == itemsToPick)
            {
                for (int i = 0; i < result.Length; i++)
                {
                    yield return new KeyValuePair<long, int>(
                        combinationId, result[i]);
                }

                combinationId++;
                break;
            }
        }
    }
}

public static void FillRow(object row, out long CombinationId, out int Value)
{
    var pair = (KeyValuePair<long, int>)row;
    CombinationId = pair.Key;
    Value = pair.Value;
}

      

        
        
        
      

    

(Based on this feature .)

Once the function is in place, creating a list of valid combinations is pretty straightforward:

DECLARE @Blocks TABLE 
(
    Qualification varchar(10) NOT NULL, 
    Block char(1) NOT NULL, 
    UNIQUE (Qualification, Block)
);

INSERT INTO @Blocks 
VALUES
    ('Biology', 'A'),
    ('Biology', 'C'), 
    ('Biology', 'D'), 
    ('Biology', 'E'),
    ('Chemistry', 'B'), 
    ('Chemistry', 'C'), 
    ('Chemistry', 'D'), 
    ('Chemistry', 'E'), 
    ('Chemistry', 'F'), 
    ('Computing', 'D'),
    ('Computing', 'F'), 
    ('Tutorial', 'A'), 
    ('Tutorial', 'B'), 
    ('Tutorial', 'E') 
;

DECLARE @Count int, @QualificationCount int;

SELECT
    @Count = Count(1),
    @QualificationCount = Count(DISTINCT Qualification)
FROM
    @Blocks
;

WITH cteNumberedBlocks As
(
    SELECT
        ROW_NUMBER() OVER (ORDER BY Qualification, Block) - 1 As RowNumber,
        Qualification,
        Block
    FROM
        @Blocks
),
cteAllCombinations As
(
    SELECT
        C.CombinationId,
        B.Qualification,
        B.Block
    FROM
        dbo.Combinations(@Count, @QualificationCount) As C
        INNER JOIN cteNumberedBlocks As B
        ON B.RowNumber = C.Value
),
cteMatchingCombinations As
(
    SELECT
        CombinationId
    FROM
        cteAllCombinations
    GROUP BY
        CombinationId
    HAVING
        Count(DISTINCT Qualification) = @QualificationCount
    And
        Count(DISTINCT Block) = @QualificationCount
)
SELECT
    DENSE_RANK() OVER(ORDER BY C.CombinationId) As CombinationNumber,
    C.Qualification,
    C.Block
FROM
    cteAllCombinations As C
    INNER JOIN cteMatchingCombinations As MC
    ON MC.CombinationId = C.CombinationId
ORDER BY
    CombinationNumber,
    Qualification
;

      

        
        
        
      

    

This query will generate a list of 172 lines representing 43 valid combinations:

1  Biology    A
1  Chemistry  B
1  Computing  D
1  Tutorial   E

2  Biology    A
2  Chemistry  B
2  Computing  F
2  Tutorial   E
...

      

        
        
        
      

    

If you want the TSQL version of the function Combinations

:

CREATE FUNCTION dbo.Combinations
(
    @TotalCount int,
    @ItemsToPick int
)
Returns @Result TABLE
(
    CombinationId bigint NOT NULL,
    ItemNumber int NOT NULL,
    Unique (CombinationId, ItemNumber)
)
As
BEGIN
DECLARE @CombinationId bigint;
DECLARE @StackPointer int, @Index int, @Value int;
DECLARE @Stack TABLE 
( 
    ID int NOT NULL Primary Key,
    Value int NOT NULL
);
DECLARE @Temp TABLE
(
    ID int NOT NULL Primary Key,
    Value int NOT NULL Unique
);

    SET @CombinationId = 1;

    SET @StackPointer = 1;
    INSERT INTO @Stack (ID, Value) VALUES (1, 0);

    WHILE @StackPointer > 0
    BEGIN
        SET @Index = @StackPointer - 1;
        DELETE FROM @Temp WHERE ID >= @Index;

        -- Pop:
        SELECT @Value = Value FROM @Stack WHERE ID = @StackPointer;
        DELETE FROM @Stack WHERE ID = @StackPointer;
        SET @StackPointer -= 1;

        WHILE @Value < @TotalCount
        BEGIN
            INSERT INTO @Temp (ID, Value) VALUES (@Index, @Value);
            SET @Index += 1;
            SET @Value += 1;

            -- Push:
            SET @StackPointer += 1;
            INSERT INTO @Stack (ID, Value) VALUES (@StackPointer, @Value);

            If @Index = @ItemsToPick
            BEGIN
                INSERT INTO @Result (CombinationId, ItemNumber)
                SELECT @CombinationId, Value
                FROM @Temp;

                SET @CombinationId += 1;
                SET @Value = @TotalCount;
            END;
        END;
    END;

    Return;
END

      

        
        
        
      

    

It's pretty much the same as the SQLCLR version, except for the fact that TSQL doesn't have stacks or arrays, so I had to fake them with table variables.