求助：如何用SQL Pivot实现按person_nbr分组的药品信息列转行

我明白你现在的困惑——直接用Pivot处理这种需要按用户分组并保留N组键值对的场景，确实需要先做一步预处理。下面结合你的示例数据，一步步帮你实现需求：

核心思路 #

要生成MedName1~5和MedDate1~5这10列，我们需要先给每个person_nbr下的药品记录按顺序编号，然后再基于这个编号做透视。直接用Pivot无法自动帮你分组编号，所以得先用窗口函数生成行号。

完整SQL实现 #

WITH ranked_medications AS (
    SELECT 
        p.person_nbr,
        psm.description,
        m.medication_name,
        m.StartDate AS MedDate,
        -- 给每个person_nbr下的药品按开始日期排序，生成1~5的行号
        ROW_NUMBER() OVER (PARTITION BY p.person_nbr ORDER BY m.StartDate) AS med_rank
    FROM person p 
    LEFT JOIN patient_medication m ON p.person_id = m.person_id 
    LEFT JOIN patient_status ps ON ps.person_id = p.person_id 
    LEFT JOIN patient_status_mstr psm ON psm.patient_status_id = ps.patient_status_id
    -- 过滤掉无药品的记录，可选，根据你的实际需求调整
    WHERE m.medication_name IS NOT NULL
)
SELECT 
    person_nbr,
    description,
    -- 透视药品名称列
    MAX(CASE WHEN med_rank = 1 THEN medication_name END) AS MedName1,
    MAX(CASE WHEN med_rank = 2 THEN medication_name END) AS MedName2,
    MAX(CASE WHEN med_rank = 3 THEN medication_name END) AS MedName3,
    MAX(CASE WHEN med_rank = 4 THEN medication_name END) AS MedName4,
    MAX(CASE WHEN med_rank = 5 THEN medication_name END) AS MedName5,
    -- 透视药品日期列
    MAX(CASE WHEN med_rank = 1 THEN MedDate END) AS MedDate1,
    MAX(CASE WHEN med_rank = 2 THEN MedDate END) AS MedDate2,
    MAX(CASE WHEN med_rank = 3 THEN MedDate END) AS MedDate3,
    MAX(CASE WHEN med_rank = 4 THEN MedDate END) AS MedDate4,
    MAX(CASE WHEN med_rank = 5 THEN MedDate END) AS MedDate5
FROM ranked_medications
GROUP BY person_nbr, description
ORDER BY person_nbr;

代码细节解释 #

1. CTE ranked_medications：

   • 用ROW_NUMBER() OVER (PARTITION BY p.person_nbr ORDER BY m.StartDate)给每个用户的药品按开始日期排序，生成唯一的med_rank（1到5，超出5的记录会被自动排除，因为后续只处理1-5的编号）。
   • 如果你想按药品名称排序，把ORDER BY m.StartDate改成ORDER BY m.medication_name即可，排序规则完全可以按需调整。

2. 透视逻辑：

   • 用CASE配合MAX聚合函数，把每个med_rank对应的药品名称和日期映射到对应的MedNameX和MedDateX列。
   • 每个用户+med_rank只会有一条记录，MAX在这里的作用是消除分组后的NULL值，确保每个单元格只保留对应的数据。

针对你的示例数据的输出结果 #

运行上面的SQL后，你的示例数据会得到这样的结果：

（注：如果同一个用户有不同的description，会分成不同的行，这是因为我们按person_nbr和description分组了。如果需要同一个用户合并成一行，你可能需要调整分组逻辑，或者确认description是否每个用户只有一个值。）

为什么不用原生PIVOT？ #

SQL的原生PIVOT更适合针对单一列的聚合透视，而我们这里需要同时透视medication_name和StartDate两列，用CASE+MAX的方式更灵活，也更容易控制每个列的映射关系，处理多列透视场景会更顺手。

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