T-SQL如何将单表列数据按值拆分至两列？求正确查询方案

你的问题出在两次左连接会产生笛卡尔积：每个医疗记录会和所有同患者的牙科记录配对，所以PatientID=1的两条医疗记录都会和唯一的牙科记录D111关联，造成重复。

要实现期望的结果，需要为每个患者的医疗、牙科记录分别分配行号，再通过行号配对避免重复。以下是正确的SQL查询：

WITH MedicalProcedures AS (
    SELECT 
        PatientID,
        OrderDate,
        ProcedureCode,
        ROW_NUMBER() OVER (PARTITION BY PatientID ORDER BY ProcedureCode) AS rn
    FROM #Procedures
    WHERE ProcedureCode NOT LIKE 'D%'
),
DentalProcedures AS (
    SELECT 
        PatientID,
        OrderDate,
        ProcedureCode,
        ROW_NUMBER() OVER (PARTITION BY PatientID ORDER BY ProcedureCode) AS rn
    FROM #Procedures
    WHERE ProcedureCode LIKE 'D%'
)
SELECT 
    COALESCE(m.PatientID, d.PatientID) AS PatientID,
    m.OrderDate AS MedOrderDate,
    m.ProcedureCode AS MedProcedure,
    d.OrderDate AS DenOrderDate,
    d.ProcedureCode AS DenProcedure
FROM MedicalProcedures m
FULL OUTER JOIN DentalProcedures d 
    ON m.PatientID = d.PatientID 
    AND m.rn = d.rn
ORDER BY COALESCE(m.PatientID, d.PatientID), COALESCE(m.rn, d.rn);

逻辑说明 #

1. CTE分表：用两个CTE分别筛选医疗（非D开头）和牙科（D开头）记录，并为每个患者的记录按ProcedureCode排序后分配行号rn。
2. 全外连接：通过PatientID和行号rn将两个CTE关联，确保每条医疗记录只匹配同序号的牙科记录，反之亦然。
3. 结果整理：用COALESCE处理患者ID和行号的空值，保证排序正确，同时保留所有医疗和牙科记录。

这个查询会生成你期望的结果，不会出现重复的牙科/医疗记录。

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