SQL Server 2016 按PairIdentity分组行转列合并成对传感器数据为单行

方案1：条件聚合（推荐，更灵活易维护） #

这是SQL行转列最通用的实现方式，对缺失的温度/湿度记录会自动返回NULL值，符合需求。
假设你的表名为SensorData，请替换为实际表名：

SELECT
    -- 若同一分组内两条记录的Created时间一致，可直接写Created放到GROUP BY中
    MAX(Created) AS Created,
    MAX(CASE WHEN Tag = 'temperature' THEN RealValue END) AS Temp,
    MAX(CASE WHEN Tag = 'temperature' THEN Units END) AS TempUnit,
    MAX(CASE WHEN Tag = 'humidity' THEN RealValue END) AS Humidity,
    MAX(CASE WHEN Tag = 'humidity' THEN Units END) AS HumidityUnit,
    PairIdentity,
    PairConfigLabel
FROM SensorData
GROUP BY PairIdentity, PairConfigLabel
-- 可选：如果要排除同时没有温度和湿度的无效分组，打开下方注释
-- HAVING MAX(CASE WHEN Tag = 'temperature' THEN RealValue END) IS NOT NULL 
--     OR MAX(CASE WHEN Tag = 'humidity' THEN RealValue END) IS NOT NULL

说明 #

• 若同一PairIdentity分组下存在多条相同Tag的记录，可先对原始数据做预处理：按PairIdentity+Tag分组取最新/符合业务规则的记录，再执行上述聚合逻辑
• 如果需要保留最早的创建时间，把MAX(Created)替换为MIN(Created)即可

方案2：PIVOT语法实现 #

SQL Server原生支持PIVOT行转列语法，但因为需要同时转数值和单位两个字段，需要做两次PIVOT拼接，可读性不如条件聚合，示例如下：

WITH TagValues AS (
    SELECT PairIdentity, PairConfigLabel, Created, Tag, RealValue, Units
    FROM SensorData
),
ValPivot AS (
    SELECT PairIdentity, PairConfigLabel, Created, [temperature] AS Temp, [humidity] AS Humidity
    FROM TagValues
    PIVOT (MAX(RealValue) FOR Tag IN ([temperature], [humidity])) AS pvt
),
UnitPivot AS (
    SELECT PairIdentity, PairConfigLabel, Created, [temperature] AS TempUnit, [humidity] AS HumidityUnit
    FROM TagValues
    PIVOT (MAX(Units) FOR Tag IN ([temperature], [humidity])) AS pvt
)
SELECT v.Created, v.Temp, u.TempUnit, v.Humidity, u.HumidityUnit, v.PairIdentity, v.PairConfigLabel
FROM ValPivot v
INNER JOIN UnitPivot u 
    ON v.PairIdentity = u.PairIdentity 
    AND v.PairConfigLabel = u.PairConfigLabel
    AND v.Created = u.Created

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