MS SQL 動態組合多表欄位：依 TB5 排序產生所有排列組合

需求

根據 TB5 定義的表名與順序（Lat），從各表取一個 Code 組合成字串，輸出所有可能組合。表結構與資料如題所示。

解法：動態 SQL + 遞歸 CTE

WITH OrderedTables AS (
    SELECT TB5Code, Lat, ROW_NUMBER() OVER (ORDER BY Lat) AS rn
    FROM TB5
),
Combinations AS (
    -- 錨點：第一張表
    SELECT CAST(TB1Code AS VARCHAR(MAX)) AS combo, 1 AS lvl
    FROM TB1
    WHERE EXISTS (SELECT 1 FROM OrderedTables WHERE rn=1 AND TB5Code='TB1')
    
    UNION ALL
    
    -- 遞歸：逐級 JOIN 下一張表
    SELECT CAST(c.combo + t.Code AS VARCHAR(MAX)), c.lvl + 1
    FROM Combinations c
    JOIN OrderedTables ot ON ot.rn = c.lvl + 1
    JOIN (
        SELECT 'TB1' AS tb, TB1Code AS Code FROM TB1
        UNION ALL SELECT 'TB2', TB2Code FROM TB2
        UNION ALL SELECT 'TB3', TB3Code FROM TB3
        UNION ALL SELECT 'TB4', TB4Code FROM TB4
    ) t ON t.tb = ot.TB5Code
    WHERE c.lvl < (SELECT MAX(rn) FROM OrderedTables)
)
SELECT combo FROM Combinations
WHERE lvl = (SELECT MAX(rn) FROM OrderedTables)
ORDER BY combo;

說明

• OrderedTables 依 Lat 排序並編號。
• 遞歸 CTE 逐級串接，最終層級等於表數量時輸出完整組合。
• 若表數量或資料行動態變更，無需修改查詢邏輯。

此法適用於 SQL Server 2008 R2 及以上版本。