求助：使用带CASE和LIKE的SQL UNPIVOT实现免硬编码动态列转行

实现逻辑说明 #

• 全程通过列名规则做自动识别，不需要手动逐一枚举待转换列：
  • 匹配所有列名包含Sales标识的列，统一归为销售类指标
  • 匹配所有列名包含Staff标识的列，统一归为人员类指标
• 区域维度两种获取路径按需选择：
  • 模式1：从匹配到的列名字符串中按规则拆分截取，适合列名结构标准化的场景
  • 模式2：关联提前维护的「指标列名-所属区域」映射表匹配，适合列名格式不统一的场景
• 自动拼接待执行的SQL语句，适配源表列增减的场景，后续不需要修改转换代码

分环境实现代码 #

SQL Server 环境实现 #

-- 动态生成UNPIVOT语句，自动识别符合规则的列
DECLARE @dynamic_sql NVARCHAR(MAX)

SET @dynamic_sql = N'
WITH valid_matched_cols AS (
    SELECT
        -- 取系统表中所有符合列名规则的列
        sc.name AS target_col,
        CASE
            WHEN sc.name LIKE ''%Sales%'' THEN ''Sales''
            WHEN sc.name LIKE ''%Staff%'' THEN ''Staff''
        END AS metric_category,
        -- 按列名格式拆分提取区域，这里的拆分逻辑可根据实际列名调整
        CASE
            WHEN sc.name LIKE ''%Sales%'' THEN LEFT(sc.name, CHARINDEX(''Sales'', sc.name) - 2)
            WHEN sc.name LIKE ''%Staff%'' THEN LEFT(sc.name, CHARINDEX(''Staff'', sc.name) - 2)
        END AS region
    FROM sys.columns sc
    WHERE
        -- 替换为实际源表名
        sc.object_id = OBJECT_ID(''你的源表完整名称'')
        -- 仅筛选带Sales/Staff标识的列
        AND (sc.name LIKE ''%Sales%'' OR sc.name LIKE ''%Staff%'')
)
SELECT
    -- 源表中保留的固定维度列，比如统计日期、渠道类型等
    t.stat_date,
    t.channel,
    mc.region,
    mc.metric_category,
    up.pivot_value
FROM 你的源表完整名称 t
UNPIVOT (
    pivot_value FOR target_col IN (
        -- 自动拼接所有匹配到的列名作为UNPIVOT的转换范围
        ' + STUFF((
            SELECT DISTINCT '', [target_col]
            FROM valid_matched_cols
            FOR XML PATH('''')
        ), 1, 1, '''') + '
    )
) up
JOIN valid_matched_cols mc ON up.target_col = mc.target_col
'

-- 执行动态生成的SQL
EXEC sp_executesql @dynamic_sql

MySQL 8.0+ 环境实现 #

-- 基于information_schema自动匹配列，动态拼接触发UNPIVOT的语句
SET @unpivot_part = CONCAT(
    'SELECT col_name,
        CASE WHEN col_name LIKE ''%Sales%'' THEN ''Sales'' WHEN col_name LIKE ''%Staff%'' THEN ''Staff'' END AS metric_category,
        CASE WHEN col_name LIKE ''%Sales%'' THEN LEFT(col_name, LOCATE(''Sales'', col_name)-2) WHEN col_name LIKE ''%Staff%'' THEN LEFT(col_name, LOCATE(''Staff'', col_name)-2) END AS region
    FROM information_schema.columns
    WHERE table_name = ''你的源表名''
    AND (col_name LIKE ''%Sales%'' OR col_name LIKE ''%Staff%'')'
);

SET @final_sql = CONCAT(
'SELECT 
    t.stat_date,
    t.channel,
    mc.region,
    mc.metric_category,
    up.metric_val
FROM 你的源表名 t
JOIN (', @unpivot_part, ') mc
JOIN JSON_TABLE(
    JSON_OBJECTAGG(t.*),
    ''$.*'' COLUMNS (
        metric_val VARCHAR(200) PATH ''$'',
        col_name VARCHAR(100) PATH ''$.key''
    )
) up ON up.col_name = mc.col_name'
);

-- 执行动态生成的语句
PREPARE dynamic_stmt FROM @final_sql;
EXECUTE dynamic_stmt;
DEALLOCATE PREPARE dynamic_stmt;

落地注意事项 #

• 拆分提取区域的代码段需要和实际列名格式匹配调整，如果列名格式不统一，建议换成关联映射表的方式，避免拆分出错
• 执行前可先将拼接好的SQL打印输出做语法校验，尤其注意列名含特殊字符的转义处理
• 源表中不需要转换的固定维度列不需要纳入动态匹配范围，直接在最终查询结果中保留即可

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