SQL Server 透视列实现按患者统计各医生就诊次数

静态Pivot方案（已知医生列表） #

如果医生列表固定，可直接用静态Pivot实现需求，以下是针对测试数据的完整查询：

DECLARE @Visits TABLE (Patient_ID INT, Age SMALLINT, Sex NVARCHAR(10), Race NVARCHAR(10), Ethnicity NVARCHAR(10), Insurance NVARCHAR(10), Physician NVARCHAR(10))
INSERT INTO @Visits (Patient_ID, Age, Sex, Race, Ethnicity, Insurance, Physician) VALUES 
(123, 60, 'Male' ,'White' ,'Not Hisp' ,'Public' ,'Phys123'),
(123, 60, 'Male' ,'White' ,'Not Hisp' ,'Public' ,'Phys123'),
(123, 60, 'Male' ,'White' ,'Not Hisp' ,'Public' ,'Phys123'),
(123, 60, 'Male' ,'White' ,'Not Hisp' ,'Public' ,'Phys456'),
(456, 40, 'Female' ,'Black' ,'Not Hisp' ,'Private' ,'Phys456'),
(456, 40, 'Female' ,'Black' ,'Not Hisp' ,'Private' ,'Phys456'),
(789, 70, 'Female' ,'White' ,'Hisp' ,'Private' ,'Phys789')

-- 静态Pivot查询
SELECT 
    Patient_ID,
    Age,
    Sex,
    Race,
    Ethnicity,
    Insurance,
    ISNULL([Susan Marshal], 0) AS [Susan Marshal],
    ISNULL([Mike Andrews], 0) AS [Mike Andrews],
    ISNULL([Michelle Bell], 0) AS [Michelle Bell]
FROM (
    -- 预处理：转换医生编号为姓名，标记单条就诊记录
    SELECT 
        Patient_ID,
        Age,
        Sex,
        Race,
        Ethnicity,
        Insurance,
        CASE Physician
            WHEN 'Phys123' THEN 'Susan Marshal'
            WHEN 'Phys456' THEN 'Mike Andrews'
            WHEN 'Phys789' THEN 'Michelle Bell'
            ELSE Physician
        END AS PhysicianName,
        1 AS VisitCount
    FROM @Visits
) AS SourceData
PIVOT (
    SUM(VisitCount) -- 聚合就诊次数
    FOR PhysicianName IN ([Susan Marshal], [Mike Andrews], [Michelle Bell]) -- 指定转列的医生姓名
) AS PivotTable
ORDER BY Patient_ID

核心步骤： #

1. 子查询预处理：将医生编号替换为真实姓名，为每条就诊记录标记VisitCount=1，方便后续统计。
2. Pivot列转换：通过PIVOT函数将医生姓名从行维度转为列维度，用SUM(VisitCount)计算患者与对应医生的就诊总次数。
3. 空值处理：用ISNULL将无就诊记录的NULL值替换为0，匹配期望输出格式。

动态Pivot方案（医生列表不固定） #

若医生列表可能变动，静态Pivot需手动修改列名，此时用动态SQL生成Pivot列更灵活：

DECLARE @Visits TABLE (Patient_ID INT, Age SMALLINT, Sex NVARCHAR(10), Race NVARCHAR(10), Ethnicity NVARCHAR(10), Insurance NVARCHAR(10), Physician NVARCHAR(10))
INSERT INTO @Visits (Patient_ID, Age, Sex, Race, Ethnicity, Insurance, Physician) VALUES 
(123, 60, 'Male' ,'White' ,'Not Hisp' ,'Public' ,'Phys123'),
(123, 60, 'Male' ,'White' ,'Not Hisp' ,'Public' ,'Phys123'),
(123, 60, 'Male' ,'White' ,'Not Hisp' ,'Public' ,'Phys123'),
(123, 60, 'Male' ,'White' ,'Not Hisp' ,'Public' ,'Phys456'),
(456, 40, 'Female' ,'Black' ,'Not Hisp' ,'Private' ,'Phys456'),
(456, 40, 'Female' ,'Black' ,'Not Hisp' ,'Private' ,'Phys456'),
(789, 70, 'Female' ,'White' ,'Hisp' ,'Private' ,'Phys789')

-- 医生映射表（实际场景可替换为业务表）
DECLARE @PhysicianMap TABLE (PhysicianID NVARCHAR(10), PhysicianName NVARCHAR(50))
INSERT INTO @PhysicianMap VALUES
('Phys123', 'Susan Marshal'),
('Phys456', 'Mike Andrews'),
('Phys789', 'Michelle Bell')

-- 动态生成Pivot列和查询列
DECLARE @PivotColumns NVARCHAR(MAX), @SelectColumns NVARCHAR(MAX)
SELECT @PivotColumns = STRING_AGG(QUOTENAME(PhysicianName), ', ')
FROM @PhysicianMap

SELECT @SelectColumns = STRING_AGG('ISNULL(' + QUOTENAME(PhysicianName) + ', 0) AS ' + QUOTENAME(PhysicianName), ', ')
FROM @PhysicianMap

-- 拼接并执行动态SQL
DECLARE @DynamicSQL NVARCHAR(MAX)
SET @DynamicSQL = N'
SELECT 
    Patient_ID,
    Age,
    Sex,
    Race,
    Ethnicity,
    Insurance,
    ' + @SelectColumns + '
FROM (
    SELECT 
        v.Patient_ID,
        v.Age,
        v.Sex,
        v.Race,
        v.Ethnicity,
        v.Insurance,
        pm.PhysicianName,
        1 AS VisitCount
    FROM @Visits v
    JOIN @PhysicianMap pm ON v.Physician = pm.PhysicianID
) AS SourceData
PIVOT (
    SUM(VisitCount)
    FOR PhysicianName IN (' + @PivotColumns + ')
) AS PivotTable
ORDER BY Patient_ID'

EXEC sp_executesql @DynamicSQL, N'@Visits TABLE (Patient_ID INT, Age SMALLINT, Sex NVARCHAR(10), Race NVARCHAR(10), Ethnicity NVARCHAR(10), Insurance NVARCHAR(10), Physician NVARCHAR(10))', @Visits = @Visits

优势： #

• 无需手动维护医生列名，医生列表更新时查询自动适配。
• 通过映射表管理编号与姓名的对应关系，更贴合实际业务逻辑。

内容的提问来源于stack exchange，提问作者alex-b12