使用SQL Server PIVOT函数实现行列转换的技术咨询

嘿，我懂PIVOT的文档有多让人头大——当初我第一次碰的时候也卡了好久！你的代码看起来是在计算单个评分项（比如Score_Greeting）的通过数、审核数和通过率，但应该是想把多个这类评分指标（比如转接、结束语评分）做行列转换对吧？咱们一步步来解决。

首先先把你给出的不完整代码补全并格式化，方便理解：

DECLARE @CallCenterID Int;
DECLARE @BeginDateofReview SmallDateTime;
DECLARE @EndDateofReview SmallDateTime;

SELECT 
    COUNT(case when Score_Greeting = 'Yes' then Score_Greeting END) AS Score_Greeting_Passed,
    SUM(CASE WHEN Score_Greeting IS NOT NULL THEN 1 ELSE 0 END) AS Score_Greeting_Reviewed,
    -- 补全通过率计算，避免除以0错误
    ROUND(CONVERT(decimal(4,1), (COUNT(CASE WHEN Score_Greeting = 'Yes' THEN Score_Greeting END) * 100.0) / NULLIF(SUM(CASE WHEN Score_Greeting IS NOT NULL THEN 1 ELSE 0 END), 0)), 1) AS Score_Greeting_Rate
-- 这里应该还有其他类似的Score_XXX项，比如Score_Transfer、Score_Closing等
FROM YourActualTableName -- 替换成你的真实表名
WHERE 
    CallCenterID = @CallCenterID
    AND ReviewDate BETWEEN @BeginDateofReview AND @EndDateofReview

接下来分两种常见的行列转换场景给你解决方案：

场景1：从宽表转窄表（把多列评分指标转成逐行统计） #

如果你的源表是宽格式（每行一条审核记录，包含多个评分列），比如：

可以用UNPIVOT先把宽表拆成窄表，再分组统计：

DECLARE @CallCenterID Int = 101;
DECLARE @BeginDateofReview SmallDateTime = '2024-01-01';
DECLARE @EndDateofReview SmallDateTime = '2024-01-31';

SELECT 
    MetricName,
    COUNT(CASE WHEN MetricValue = 'Yes' THEN 1 END) AS Passed,
    COUNT(MetricValue) AS Reviewed, -- 自动排除NULL值
    ROUND(CONVERT(decimal(4,1), COUNT(CASE WHEN MetricValue = 'Yes' THEN 1 END) * 100.0 / NULLIF(COUNT(MetricValue), 0)), 1) AS Rate
FROM (
    -- 第一步：把多列评分转成每行一个评分项
    SELECT 
        CallCenterID,
        ReviewDate,
        MetricName,
        MetricValue
    FROM YourActualTableName
    UNPIVOT (
        MetricValue FOR MetricName IN (Score_Greeting, Score_Transfer, Score_Closing) -- 列出所有需要统计的评分列
    ) AS unpvt
) AS PivotSource
WHERE 
    CallCenterID = @CallCenterID
    AND ReviewDate BETWEEN @BeginDateofReview AND @EndDateofReview
GROUP BY MetricName

场景2：从窄表转宽表（把逐行统计转成多列展示） #

如果你的源表是窄格式（每行是一个评分项的记录），比如：

可以用PIVOT把统计结果转成宽格式：

DECLARE @CallCenterID Int = 101;
DECLARE @BeginDateofReview SmallDateTime = '2024-01-01';
DECLARE @EndDateofReview SmallDateTime = '2024-01-31';

SELECT 
    [Score_Greeting_Passed], [Score_Greeting_Reviewed], [Score_Greeting_Rate],
    [Score_Transfer_Passed], [Score_Transfer_Reviewed], [Score_Transfer_Rate]
FROM (
    -- 第一步：先计算每个评分项的三类统计，给统计项加后缀
    SELECT 
        CONCAT(MetricName, '_Passed') AS MetricStat,
        COUNT(CASE WHEN MetricValue = 'Yes' THEN 1 END) AS StatValue
    FROM YourActualTableName
    WHERE 
        CallCenterID = @CallCenterID
        AND ReviewDate BETWEEN @BeginDateofReview AND @EndDateofReview
    GROUP BY MetricName
    UNION ALL
    SELECT 
        CONCAT(MetricName, '_Reviewed') AS MetricStat,
        COUNT(MetricValue) AS StatValue
    FROM YourActualTableName
    WHERE 
        CallCenterID = @CallCenterID
        AND ReviewDate BETWEEN @BeginDateofReview AND @EndDateofReview
    GROUP BY MetricName
    UNION ALL
    SELECT 
        CONCAT(MetricName, '_Rate') AS MetricStat,
        ROUND(CONVERT(decimal(4,1), COUNT(CASE WHEN MetricValue = 'Yes' THEN 1 END) * 100.0 / NULLIF(COUNT(MetricValue), 0)), 1) AS StatValue
    FROM YourActualTableName
    WHERE 
        CallCenterID = @CallCenterID
        AND ReviewDate BETWEEN @BeginDateofReview AND @EndDateofReview
    GROUP BY MetricName
) AS PivotSource
PIVOT (
    MAX(StatValue) FOR MetricStat IN (
        [Score_Greeting_Passed], [Score_Greeting_Reviewed], [Score_Greeting_Rate],
        [Score_Transfer_Passed], [Score_Transfer_Reviewed], [Score_Transfer_Rate]
    )
) AS pvt

小提示 #

• 记得把代码里的YourActualTableName替换成你的真实表名
• 如果评分项特别多，可以用动态SQL自动生成PIVOT/UNPIVOT的列列表，不用手动写所有项
• NULLIF(COUNT(MetricValue), 0)是为了避免出现除以0的错误，确保没有审核记录的评分项不会报错

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