SQL Server查询：将一对多关系数据展示在单行中

没问题，我来帮你实现这个需求！你要做的是把关联的三张表数据整合，将同一个处方下的多个药品ID横向展示在单行的不同列里，也就是行转列的操作。下面给你两种实用的SQL Server实现方案：

方案一：使用条件聚合（兼容性好，适合所有SQL Server版本） #

这种方法通过给每个处方下的药品编号，再配合CASE和聚合函数来实现列转换，非常直观：

SELECT
    p.FirstName,
    p.LastName,
    pr.Id AS PrescriptionId,
    MAX(CASE WHEN rn = 1 THEN mfp.MedicineID END) AS Medicine1,
    MAX(CASE WHEN rn = 2 THEN mfp.MedicineID END) AS Medicine2,
    MAX(CASE WHEN rn = 3 THEN mfp.MedicineID END) AS Medicine3
FROM
    Patient p
JOIN
    Prescription pr ON p.Id = pr.PatientId
JOIN (
    -- 给每个处方下的药品按ID排序并分配序号
    SELECT 
        MedicineID, 
        prescriptionId,
        ROW_NUMBER() OVER (PARTITION BY prescriptionId ORDER BY Id) AS rn
    FROM MedicineForPrescription
) mfp ON pr.Id = mfp.prescriptionId
GROUP BY
    p.FirstName, p.LastName, pr.Id

方案二：使用SQL Server PIVOT运算符（语法更简洁） #

如果你的SQL Server版本支持PIVOT（2005及以上），可以用这个更简洁的写法：

SELECT
    p.FirstName,
    p.LastName,
    pr.Id AS PrescriptionId,
    [1] AS Medicine1,
    [2] AS Medicine2,
    [3] AS Medicine3
FROM
    Patient p
JOIN
    Prescription pr ON p.Id = pr.PatientId
JOIN (
    -- 同样先给药品分配序号
    SELECT 
        MedicineID, 
        prescriptionId,
        ROW_NUMBER() OVER (PARTITION BY prescriptionId ORDER BY Id) AS rn
    FROM MedicineForPrescription
) mfp ON pr.Id = mfp.prescriptionId
PIVOT (
    -- 聚合获取对应序号的药品ID
    MAX(MedicineID)
    FOR rn IN ([1], [2], [3]) -- 指定要转换的列值
) AS PivotTable

补充说明 #

如果你的业务中一个处方下的药品数量不固定（可能超过3种），上面的固定列方案就不适用了，这时候需要写动态SQL来自动生成对应数量的列。不过从你的示例数据来看，固定3列的方案完全能满足需求。

备注：内容来源于stack exchange，提问作者MD SHAFFAT