如何高效实现表透视？求优化重复SQL CASE语句的方法

我现在手动实现了小数据量的表透视，但担心处理大表时，团队要添加大量重复语句，导致当前工作失去价值。就像下面的SQL查询，我只修改了example_id的值，却重复写了大量相同结构的语句，想问能不能用函数来优化？

原SQL查询：

select
    id,
    MAX(CASE WHEN example_id = 0 THEN 1 ELSE 0 END) AS "example_id",
    MAX(CASE WHEN example_id  = 0 THEN example_name END) AS "example_name ",
    MAX(CASE WHEN example_id = 0 AND type = 'data' THEN type END) AS "data",
    
    MAX(CASE WHEN example_id = 2 THEN 1 ELSE 0 END) AS "example_id ",
    MAX(CASE WHEN example_id = 2 THEN example_name END) AS "example_name ",
    MAX(CASE WHEN example_id = 2 AND type = 'data' THEN type END) AS "data",
    
    MAX(CASE WHEN example_id = 3 THEN 1 ELSE 0 END) AS "example_id ",
    MAX(CASE WHEN example_id = 3 THEN example_name END) AS "example_name  ",
    MAX(CASE WHEN example_id = 3 AND type = 'data' THEN type END) AS "data"
from
    table
group by
    id;

1. 用自定义函数封装重复逻辑 #

可以创建函数生成单组example_id对应的透视字段片段，再通过动态SQL拼接执行。以MySQL为例：

第一步：创建生成字段的函数

DELIMITER //
CREATE FUNCTION generate_pivot_cols(p_example_id INT)
RETURNS TEXT
DETERMINISTIC
BEGIN
    RETURN CONCAT(
        'MAX(CASE WHEN example_id = ', p_example_id, ' THEN 1 ELSE 0 END) AS "example_id_', p_example_id, '",',
        'MAX(CASE WHEN example_id = ', p_example_id, ' THEN example_name END) AS "example_name_', p_example_id, '",',
        'MAX(CASE WHEN example_id = ', p_example_id, ' AND type = ''data'' THEN type END) AS "data_', p_example_id, '",'
    );
END //
DELIMITER ;

第二步：动态拼接并执行SQL

SET @sql = CONCAT(
    'SELECT id, ',
    generate_pivot_cols(0),
    generate_pivot_cols(2),
    generate_pivot_cols(3),
    ' FROM `table` GROUP BY id'
);
-- 移除末尾多余的逗号
SET @sql = LEFT(@sql, LENGTH(@sql) - 1);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

2. 自动遍历所有example_id（无需手动指定） #

如果需要自动处理表中所有存在的example_id，可以查询去重后的example_id来批量生成字段：

SET @cols = '';
SELECT GROUP_CONCAT(
    CONCAT(
        'MAX(CASE WHEN example_id = ', example_id, ' THEN 1 ELSE 0 END) AS "example_id_', example_id, '",',
        'MAX(CASE WHEN example_id = ', example_id, ' THEN example_name END) AS "example_name_', example_id, '",',
        'MAX(CASE WHEN example_id = ', example_id, ' AND type = ''data'' THEN type END) AS "data_', example_id, '"'
    )
) INTO @cols
FROM (SELECT DISTINCT example_id FROM `table`) AS ids;

SET @sql = CONCAT('SELECT id, ', @cols, ' FROM `table` GROUP BY id');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

3. 用数据库原生PIVOT功能（适用于SQL Server/Oracle等） #

如果使用支持原生PIVOT的数据库，可以简化透视逻辑。以SQL Server为例：

SELECT id,
    [0_example_id], [0_example_name], [0_data],
    [2_example_id], [2_example_name], [2_data],
    [3_example_id], [3_example_name], [3_data]
FROM (
    SELECT 
        id,
        CONCAT(example_id, '_example_id') AS col_name,
        1 AS col_value
    FROM [table]
    UNION ALL
    SELECT 
        id,
        CONCAT(example_id, '_example_name') AS col_name,
        example_name AS col_value
    FROM [table]
    UNION ALL
    SELECT 
        id,
        CONCAT(example_id, '_data') AS col_name,
        CASE WHEN type = 'data' THEN type END AS col_value
    FROM [table]
) AS src
PIVOT (
    MAX(col_value)
    FOR col_name IN (
        [0_example_id], [0_example_name], [0_data],
        [2_example_id], [2_example_name], [2_data],
        [3_example_id], [3_example_name], [3_data]
    )
) AS pvt;

关键注意事项 #

• 原SQL中存在重复别名（如多个example_id），会导致执行报错，优化时需给别名加上example_id后缀区分唯一性。
• 动态SQL要注意防范SQL注入风险，若example_id来自用户输入，必须做合法性校验。
• 不同数据库的函数、动态SQL语法存在差异，需根据实际使用的数据库（如PostgreSQL、MySQL、SQL Server）调整代码。

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