IBM DB2动态透视表实现：按用户最大用药数生成med列

核心思路 #

要实现将每个用户的多行药品记录转为动态列（med_1至med_n，最多8种），需要分两步：

1. 为每个用户的药品分配序号，标记其为第N种药
2. 通过动态SQL自动生成对应数量的列，完成行转列

步骤1：为药品分配序号 #

先通过窗口函数ROW_NUMBER()给每个用户的药品按编码排序并分配序号：

SELECT 
    hp.id_user,
    hp.diagnosis_date,
    hp.mot,
    m.name AS med_name,
    -- 按用户分区，药品编码排序，生成序号
    ROW_NUMBER() OVER (PARTITION BY hp.id_user ORDER BY m.cod) AS med_seq
FROM salud.high_price hp
JOIN salud.medicines_user mu ON hp.id_user = mu.id_user
JOIN salud.medicine m ON mu.cod_medicine = m.cod

步骤2：动态生成列（用存储过程实现） #

由于DB2原生不支持直接在单条SQL中动态生成列，可通过存储过程拼接动态SQL来实现，同时限制最多生成8列：

CREATE OR REPLACE PROCEDURE GET_USER_MEDS()
LANGUAGE SQL
DYNAMIC RESULT SETS 1
BEGIN
    DECLARE max_meds INT;
    DECLARE sql_stmt VARCHAR(32000);
    DECLARE i INT DEFAULT 1;
    
    -- 获取用户的最大用药数，最多限制为8种
    SELECT LEAST(MAX(med_count), 8) INTO max_meds
    FROM (
        SELECT id_user, COUNT(*) AS med_count
        FROM salud.medicines_user
        GROUP BY id_user
    ) t;
    
    -- 初始化SQL基础部分
    SET sql_stmt = 'SELECT hp.id_user, hp.diagnosis_date, hp.mot';
    
    -- 循环生成med_1到med_n的列定义
    WHILE i <= max_meds DO
        SET sql_stmt = sql_stmt || ', MAX(CASE WHEN med_seq = ' || i || ' THEN med_name ELSE NULL END) AS med_' || i;
        SET i = i + 1;
    END WHILE;
    
    -- 拼接子查询和分组逻辑
    SET sql_stmt = sql_stmt || ' FROM (
        SELECT 
            hp.id_user,
            hp.diagnosis_date,
            hp.mot,
            m.name AS med_name,
            ROW_NUMBER() OVER (PARTITION BY hp.id_user ORDER BY m.cod) AS med_seq
        FROM salud.high_price hp
        JOIN salud.medicines_user mu ON hp.id_user = mu.id_user
        JOIN salud.medicine m ON mu.cod_medicine = m.cod
    ) t GROUP BY id_user, diagnosis_date, mot ORDER BY id_user';
    
    -- 执行动态SQL并返回结果集
    PREPARE stmt FROM sql_stmt;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END@

调用存储过程获取结果 #

执行以下命令即可得到期望的动态列结果：

CALL GET_USER_MEDS()@

替代方案：客户端动态拼接SQL #

如果不想用存储过程，也可以在应用客户端（如Python、Java）中先查询最大用药数，再拼接SQL语句执行：

1. 先查询最大用药数：

   SELECT LEAST(MAX(med_count), 8) AS max_meds
   FROM (
       SELECT id_user, COUNT(*) AS med_count
       FROM salud.medicines_user
       GROUP BY id_user
   ) t
   

2. 根据返回的max_meds值，循环生成MAX(CASE WHEN med_seq = N THEN med_name ELSE NULL END) AS med_N的列片段，拼接成完整SQL后执行。

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