如何用Pivot将SQL表列转置为行展示系统任务状态

原查询语句 #

select ID,
       Systemname,
       Mode,
       Evtdate,
       Sattus,
       Desc
From dbo.MainTasksStatus;

原表查询结果 #

解决方案 #

针对按SystemName分组、展示每个系统对应Mode1/Mode2/Mode3的需求，提供两种实现方式，其中条件聚合在多字段场景下更灵活：

1. 条件聚合（推荐） #

这种方式无需使用PIVOT关键字，通过CASE表达式结合聚合函数实现，适合同时展示多个字段（如Status、Desc、EvtDate）的场景：

SELECT 
    SystemName,
    -- Mode1 相关字段
    ISNULL(MAX(CASE WHEN Mode = 'Mode1' THEN Status END), '无数据') AS Mode1_Status,
    ISNULL(MAX(CASE WHEN Mode = 'Mode1' THEN [Desc] END), '无数据') AS Mode1_Desc,
    ISNULL(MAX(CASE WHEN Mode = 'Mode1' THEN EvtDate END), '无数据') AS Mode1_EvtDate,
    -- Mode2 相关字段
    ISNULL(MAX(CASE WHEN Mode = 'Mode2' THEN Status END), '无数据') AS Mode2_Status,
    ISNULL(MAX(CASE WHEN Mode = 'Mode2' THEN [Desc] END), '无数据') AS Mode2_Desc,
    ISNULL(MAX(CASE WHEN Mode = 'Mode2' THEN EvtDate END), '无数据') AS Mode2_EvtDate,
    -- Mode3 相关字段
    ISNULL(MAX(CASE WHEN Mode = 'Mode3' THEN Status END), '无数据') AS Mode3_Status,
    ISNULL(MAX(CASE WHEN Mode = 'Mode3' THEN [Desc] END), '无数据') AS Mode3_Desc,
    ISNULL(MAX(CASE WHEN Mode = 'Mode3' THEN EvtDate END), '无数据') AS Mode3_EvtDate
FROM dbo.MainTasksStatus
GROUP BY SystemName;

说明：

• 用ISNULL将缺失模式的NULL替换为自定义提示（如'无数据'），也可直接保留NULL
• 因每个SystemName+Mode组合唯一，使用MAX/MIN聚合函数均可

2. 使用PIVOT关键字 #

如果必须使用PIVOT，单字段场景写法简洁；多字段场景需先合并字段再透视：

单字段透视（仅展示Status）

SELECT 
    SystemName,
    ISNULL([Mode1], '无数据') AS Mode1_Status,
    ISNULL([Mode2], '无数据') AS Mode2_Status,
    ISNULL([Mode3], '无数据') AS Mode3_Status
FROM (
    SELECT SystemName, Mode, Status
    FROM dbo.MainTasksStatus
) AS SourceData
PIVOT (
    MAX(Status)
    FOR Mode IN ([Mode1], [Mode2], [Mode3])
) AS PivotTable;

多字段透视（展示Status和Desc）

SELECT 
    SystemName,
    ISNULL(Mode1_Status, '无数据') AS Mode1_Status,
    ISNULL(Mode1_Desc, '无数据') AS Mode1_Desc,
    ISNULL(Mode2_Status, '无数据') AS Mode2_Status,
    ISNULL(Mode2_Desc, '无数据') AS Mode2_Desc,
    ISNULL(Mode3_Status, '无数据') AS Mode3_Status,
    ISNULL(Mode3_Desc, '无数据') AS Mode3_Desc
FROM (
    SELECT 
        SystemName,
        Mode + '_Status' AS PivotField,
        CAST(Status AS VARCHAR(10)) AS FieldValue
    FROM dbo.MainTasksStatus
    UNION ALL
    SELECT 
        SystemName,
        Mode + '_Desc' AS PivotField,
        [Desc] AS FieldValue
    FROM dbo.MainTasksStatus
) AS SourceData
PIVOT (
    MAX(FieldValue)
    FOR PivotField IN ([Mode1_Status], [Mode1_Desc], [Mode2_Status], [Mode2_Desc], [Mode3_Status], [Mode3_Desc])
) AS PivotTable;

示例输出（条件聚合方式） #

内容的提问来源于stack exchange，提问作者a.Paryab