如何简化生成SQL Server中AA-ZZ含NULL的所有组合表？

I get it—manually enumerating every two-letter combination from AA to ZZ is tedious, and scaling that to all possible subsets (single values, pairs, triples, etc.) is a nightmare. Let's simplify this with dynamic generation and recursive logic.

Step 1: Automatically Generate AA-ZZ Base Values #

First, we'll create a CTE to generate all 676 two-letter combinations without manually writing hundreds of UNION statements:

WITH 
CTE_Chars AS (
    -- Generate each letter from A to Z using ASCII values
    SELECT CHAR(ASCII('A') + n) AS CharVal
    FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24),(25)) AS t(n)
),
CTE_TwoLetter AS (
    -- Cross join to get all AA-ZZ combinations
    SELECT c1.CharVal + c2.CharVal AS Value
    FROM CTE_Chars c1
    CROSS JOIN CTE_Chars c2
)
SELECT * FROM CTE_TwoLetter; -- Verify we have all 676 values

This gives us every possible two-letter string from AA to ZZ in one go, no manual typing required.

Option 1: Store Combinations in a Single Field (Recommended for All Subsets) #

If you're okay with storing combinations as comma-separated strings (which is far more scalable for large subsets), use a recursive CTE to generate every possible non-duplicate subset (single values, pairs, triples, up to all 676 values) plus a NULL row:

WITH 
CTE_Chars AS (
    SELECT CHAR(ASCII('A') + n) AS CharVal
    FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24),(25)) AS t(n)
),
CTE_TwoLetter AS (
    SELECT 
        c1.CharVal + c2.CharVal AS Value,
        ROW_NUMBER() OVER (ORDER BY c1.CharVal, c2.CharVal) AS RN -- Assign unique ID for recursion
    FROM CTE_Chars c1
    CROSS JOIN CTE_Chars c2
),
CTE_Subsets AS (
    -- Base case: single-value combinations
    SELECT 
        CAST(Value AS VARCHAR(MAX)) AS CombinedValue,
        RN
    FROM CTE_TwoLetter

    UNION ALL

    -- Recursive case: build larger combinations by adding only higher-order values (avoids duplicates)
    SELECT 
        CAST(s.CombinedValue + ', ' + t.Value AS VARCHAR(MAX)) AS CombinedValue,
        t.RN
    FROM CTE_Subsets s
    INNER JOIN CTE_TwoLetter t ON t.RN > s.RN
)
-- Final result: all subsets + NULL value
SELECT CombinedValue FROM CTE_Subsets
UNION ALL
SELECT NULL AS CombinedValue
ORDER BY CombinedValue
OPTION (MAXRECURSION 0); -- Required since we need more than 100 recursion levels

Key Notes for This Option: #

• The OPTION (MAXRECURSION 0) is mandatory here—SQL Server's default recursion limit is 100, and we need up to 675 levels to generate all subsets.
• This generates all possible non-duplicate combinations (e.g., "AA, AB" but not "AB, AA") since we only add values with higher RN than the current subset.
• The VARCHAR(MAX) type ensures we can even store the full set of 676 values without length issues.

Option 2: Split Combinations Into Multiple Columns (Like Your Original Approach) #

If you need the split-column format (with NULLs for unused positions), you can reuse the CTE_TwoLetter CTE instead of your manual T_VALUE table. Here's how to extend it for up to 4 columns (you can add more UNION ALL blocks for additional columns if needed):

WITH 
CTE_Chars AS (
    SELECT CHAR(ASCII('A') + n) AS CharVal
    FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24),(25)) AS t(n)
),
CTE_TwoLetter AS (
    SELECT c1.CharVal + c2.CharVal AS Value
    FROM CTE_Chars c1
    CROSS JOIN CTE_Chars c2
)
-- Single-value rows
SELECT Value AS Value_1, NULL AS Value_2, NULL AS Value_3, NULL AS Value_4 FROM CTE_TwoLetter
UNION ALL
-- Two-value combinations (sorted to avoid duplicates)
SELECT A.Value, B.Value, NULL, NULL 
FROM CTE_TwoLetter A 
CROSS JOIN CTE_TwoLetter B 
WHERE A.Value < B.Value
UNION ALL
-- Three-value combinations
SELECT A.Value, B.Value, C.Value, NULL 
FROM CTE_TwoLetter A 
CROSS JOIN CTE_TwoLetter B 
CROSS JOIN CTE_TwoLetter C 
WHERE A.Value < B.Value AND B.Value < C.Value
UNION ALL
-- Four-value combinations
SELECT A.Value, B.Value, C.Value, D.Value 
FROM CTE_TwoLetter A 
CROSS JOIN CTE_TwoLetter B 
CROSS JOIN CTE_TwoLetter C 
CROSS JOIN CTE_TwoLetter D 
WHERE A.Value < B.Value AND B.Value < C.Value AND C.Value < D.Value
UNION ALL
-- Optional: add a full-NULL row if needed
SELECT NULL, NULL, NULL, NULL
ORDER BY Value_1, Value_2, Value_3, Value_4;

Important Caveat for This Option: #

Combination counts grow exponentially. For example, 4-column combinations would be ~13 billion rows (calculated as C(676,4)), which is likely impractical to store or generate. Use this only if you need a limited number of columns (e.g., 2 or 3).

内容的提问来源于stack exchange，提问作者Andreas