如何动态按组遍历列实现数据Unpivot？

针对测试次数不固定的宽表（成对的Test Date和Test Results列），可以通过动态SQL实现自动匹配列对并转换为窄表格式，无需手动修改代码适配新增列。以下是分数据库的具体实现方案：

核心思路 #

1. 从information_schema中提取所有Test Date和Test Results列，按编号配对（需保证列名格式统一，如TestDate1对应TestResult1）
2. 动态构建Unpivot逻辑，将每对列转换为一行记录
3. 过滤空测试记录，输出最终的窄表结构

方案一：SQL Server（推荐用CROSS APPLY VALUES） #

此方式仅扫描一次原表，效率优于多次UNION ALL：

DECLARE @SQL NVARCHAR(MAX)
DECLARE @ValuesClause NVARCHAR(MAX)

-- 自动生成成对列的VALUES子句
SELECT @ValuesClause = STRING_AGG(
    CONCAT('(', QUOTENAME(c1.COLUMN_NAME), ', ', QUOTENAME(c2.COLUMN_NAME), ')'),
    ', '
)
FROM INFORMATION_SCHEMA.COLUMNS c1
JOIN INFORMATION_SCHEMA.COLUMNS c2 
    ON c1.TABLE_NAME = c2.TABLE_NAME
    AND c1.TABLE_SCHEMA = c2.TABLE_SCHEMA
    -- 匹配列名中的编号（根据实际列名格式调整截取规则）
    AND SUBSTRING(c1.COLUMN_NAME, 8, LEN(c1.COLUMN_NAME)-7) = SUBSTRING(c2.COLUMN_NAME, 11, LEN(c2.COLUMN_NAME)-10)
WHERE c1.TABLE_NAME = 'PatientTests'  -- 替换为你的表名
    AND c1.COLUMN_NAME LIKE 'TestDate%'
    AND c2.COLUMN_NAME LIKE 'TestResult%'

-- 构建最终查询语句
SET @SQL = CONCAT('
SELECT 
    MRN,
    [Dx Date],
    TestDate,
    TestResult
FROM PatientTests
CROSS APPLY (
    VALUES ', @ValuesClause, '
) AS Unpivoted(TestDate, TestResult)
WHERE TestDate IS NOT NULL  -- 过滤无测试记录的行
')

-- 执行动态SQL
EXEC sp_executesql @SQL

方案二：MySQL（用GROUP_CONCAT生成动态语句） #

MySQL无STRING_AGG，改用GROUP_CONCAT实现：

SET @ValuesClause = (
    SELECT GROUP_CONCAT(
        CONCAT('(`', c1.COLUMN_NAME, '`, `', c2.COLUMN_NAME, '`)')
        SEPARATOR ', '
    )
    FROM INFORMATION_SCHEMA.COLUMNS c1
    JOIN INFORMATION_SCHEMA.COLUMNS c2 
        ON c1.TABLE_SCHEMA = c2.TABLE_SCHEMA
        AND c1.TABLE_NAME = c2.TABLE_NAME
        AND SUBSTRING(c1.COLUMN_NAME, 8) = SUBSTRING(c2.COLUMN_NAME, 11)
    WHERE c1.TABLE_SCHEMA = 'your_schema'  -- 替换为你的库名
        AND c1.TABLE_NAME = 'PatientTests'
        AND c1.COLUMN_NAME LIKE 'TestDate%'
        AND c2.COLUMN_NAME LIKE 'TestResult%'
);

SET @SQL = CONCAT('
SELECT 
    MRN,
    `Dx Date`,
    TestDate,
    TestResult
FROM PatientTests
CROSS JOIN UNNEST(ARRAY[', @ValuesClause, ']) AS Unpivoted(TestDate, TestResult)
WHERE TestDate IS NOT NULL
');

-- 执行动态SQL
PREPARE stmt FROM @SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

关键注意事项 #

• 列名格式统一：必须保证Test Date和Test Results的编号规则一致（如TestDate_3对应TestResult_3），否则无法正确配对
• 权限要求：执行动态SQL需要EXECUTE权限，以及访问information_schema的权限
• 封装复用：可以将上述逻辑封装为存储过程，每次调用自动适配最新的列结构
• 视图替代方案：若需要持久化视图，可通过存储过程动态生成视图脚本（视图本身无法直接包含动态SQL）

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