SQL Server行转列实现：SCADA关联数据库的泵数据转换需求

核心思路 #

既然动态SQL无法适配且SCADA权限存疑，优先采用静态SQL结合CASE表达式实现列转行，同时保留后续关联其他表的扩展性。

分步实现 #

1. 基础列转行查询（覆盖固定泵数量范围） #

先按站点最大可能的泵数设计（比如先设10个，可按需调整），用CASE语句将每个泵的运行时长映射到对应列：

SELECT
    s.StationID,
    s.StationName,
    prh.Date,
    MAX(CASE WHEN prh.PumpNumber = 1 THEN prh.RunHours ELSE NULL END) AS [Pump 1],
    MAX(CASE WHEN prh.PumpNumber = 2 THEN prh.RunHours ELSE NULL END) AS [Pump 2],
    MAX(CASE WHEN prh.PumpNumber = 3 THEN prh.RunHours ELSE NULL END) AS [Pump 3],
    -- 按需追加更多泵的CASE语句，覆盖站点最大泵数
    MAX(CASE WHEN prh.PumpNumber = 10 THEN prh.RunHours ELSE NULL END) AS [Pump 10]
FROM
    Stations s
LEFT JOIN
    PumpsRH prh ON s.StationID = prh.StationID
GROUP BY
    s.StationID, s.StationName, prh.Date
ORDER BY
    s.StationID, prh.Date;

• 用LEFT JOIN确保站点即使某天部分泵无数据，仍能展示基础信息
• MAX()用于分组后提取对应泵的运行时长，若数据唯一也可替换为SUM()或直接CASE

2. 适配动态泵数量（无需动态SQL） #

结合Stations表的PumpCount字段，只生成站点实际拥有的泵列：

SELECT
    s.StationID,
    s.StationName,
    prh.Date,
    CASE WHEN s.PumpCount >= 1 THEN MAX(CASE WHEN prh.PumpNumber = 1 THEN prh.RunHours END) END AS [Pump 1],
    CASE WHEN s.PumpCount >= 2 THEN MAX(CASE WHEN prh.PumpNumber = 2 THEN prh.RunHours END) END AS [Pump 2],
    CASE WHEN s.PumpCount >= 3 THEN MAX(CASE WHEN prh.PumpNumber = 3 THEN prh.RunHours END) END AS [Pump 3],
    CASE WHEN s.PumpCount >= 4 THEN MAX(CASE WHEN prh.PumpNumber = 4 THEN prh.RunHours END) END AS [Pump 4],
    CASE WHEN s.PumpCount >= 5 THEN MAX(CASE WHEN prh.PumpNumber = 5 THEN prh.RunHours END) END AS [Pump 5]
FROM
    Stations s
LEFT JOIN
    PumpsRH prh ON s.StationID = prh.StationID
GROUP BY
    s.StationID, s.StationName, s.PumpCount, prh.Date
ORDER BY
    s.StationID, prh.Date;

• 无对应泵的列会返回NULL，多数SCADA平台支持通过界面配置隐藏空列

3. 后续关联其他表的扩展性 #

要关联其他表（如泵状态表、传感器表），直接在JOIN中添加关联条件即可，不影响列转行逻辑：

SELECT
    s.StationID,
    s.StationName,
    prh.Date,
    MAX(CASE WHEN prh.PumpNumber = 1 THEN prh.RunHours END) AS [Pump 1],
    MAX(CASE WHEN prh.PumpNumber = 2 THEN prh.RunHours END) AS [Pump 2],
    -- 关联泵状态表示例
    MAX(CASE WHEN prh.PumpNumber = 1 THEN ps.Status END) AS [Pump 1 Status]
FROM
    Stations s
LEFT JOIN
    PumpsRH prh ON s.StationID = prh.StationID
LEFT JOIN
    PumpStatus ps ON prh.StationID = ps.StationID AND prh.PumpNumber = ps.PumpNumber AND prh.Date = ps.Date
GROUP BY
    s.StationID, s.StationName, prh.Date
ORDER BY
    s.StationID, prh.Date;

SCADA平台适配建议 #

• 若SCADA不支持动态列，先确认平台最大支持列数，提前写好足够数量的CASE语句，空列通过界面配置隐藏
• 可将上述逻辑封装为存储过程，多数SCADA允许执行存储过程，权限要求比动态SQL低
• 测试时先针对单个站点验证结果，再扩展到全站点，确保数据准确

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