如何通过动态Pivot按分组将聚合剂量数据转换为单行

需求说明 #

熟悉Pivot操作，但需将已聚合的剂量数据按ID和药物类型合并为单行记录。其中：

• T0：首日总剂量
• T5：第5天总剂量
• Total_dose：全疗程总剂量
  由于药物种类可达上百种，需实现动态处理，无需手动指定药物类型。

输入数据SQL示例 #

with samp as (select 1 as ID, 'A' as medication, 6 as T0, 8 as T5, 35 as total_dose from dual union all
         select 1 as ID, 'B' as medication, 3 as T0, 2 as T5, 15 as total_dose from dual union all
         select 2 as ID, 'A' as medication, 6 as T0, NULL as T5, 18 as total_dose from dual union all
         select 2 as ID, 'C' as medication, 100 as T0, 120 as T5, 550 as total_dose from dual)
         
select * from samp;

当前数据格式 #

期望输出格式 #

解决方案 #

1. 静态行转列（固定药物类型） #

如果药物类型固定，可直接用CASE语句+聚合函数实现：

SELECT ID,
       MAX(CASE WHEN medication = 'A' THEN T0 END) AS T0_A,
       MAX(CASE WHEN medication = 'A' THEN T5 END) AS T5_A,
       MAX(CASE WHEN medication = 'A' THEN total_dose END) AS Total_dose_A,
       MAX(CASE WHEN medication = 'B' THEN T0 END) AS T0_B,
       MAX(CASE WHEN medication = 'B' THEN T5 END) AS T5_B,
       MAX(CASE WHEN medication = 'B' THEN total_dose END) AS Total_dose_B,
       MAX(CASE WHEN medication = 'C' THEN T0 END) AS T0_C,
       MAX(CASE WHEN medication = 'C' THEN T5 END) AS T5_C,
       MAX(CASE WHEN medication = 'C' THEN total_dose END) AS Total_dose_C
FROM samp
GROUP BY ID;

2. 动态行转列（自动适配所有药物） #

针对药物种类多且不固定的场景，需用动态SQL自动生成列：

Oracle 实现

DECLARE
    v_sql VARCHAR2(4000);
    v_cols VARCHAR2(4000);
BEGIN
    -- 生成所有药物对应的列表达式
    SELECT LISTAGG(
             'MAX(CASE WHEN medication = ''' || medication || ''' THEN T0 END) AS T0_' || medication || ', ' ||
             'MAX(CASE WHEN medication = ''' || medication || ''' THEN T5 END) AS T5_' || medication || ', ' ||
             'MAX(CASE WHEN medication = ''' || medication || ''' THEN total_dose END) AS Total_dose_' || medication,
             ', '
           ) WITHIN GROUP (ORDER BY medication)
    INTO v_cols
    FROM (SELECT DISTINCT medication FROM samp);

    -- 拼接完整SQL并执行
    v_sql := 'SELECT ID, ' || v_cols || ' FROM samp GROUP BY ID';
    EXECUTE IMMEDIATE v_sql;
END;
/

MySQL 实现

SET @sql = NULL;
-- 生成动态列片段
SELECT GROUP_CONCAT(
           DISTINCT CONCAT(
               'MAX(CASE WHEN medication = ''', medication, ''' THEN T0 END) AS T0_', medication, ',',
               'MAX(CASE WHEN medication = ''', medication, ''' THEN T5 END) AS T5_', medication, ',',
               'MAX(CASE WHEN medication = ''', medication, ''' THEN total_dose END) AS Total_dose_', medication
           )
       ) INTO @sql
FROM samp;

-- 拼接并执行动态SQL
SET @sql = CONCAT('SELECT ID, ', @sql, ' FROM samp GROUP BY ID');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

说明 #

动态SQL会自动扫描数据中的所有药物类型，生成对应的列名和计算逻辑，无需手动维护药物列表。不同数据库的动态SQL语法略有差异，需根据实际使用的数据库调整。

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