sql - SQL 对列中具有相同数据的行进行分组

Question

我的数据是什么样的：

它是同时生成的 5 个数据点。我想将所有五个组合成一排。

我尝试使用以下查询：

SELECT 
    DateAndTime as Time, 
    (SELECT Val WHERE TagIndex = 0) as Normalized_Vac,
    (SELECT Val WHERE TagIndex = 1) as Avgerage_Vac,
    (SELECT Val WHERE TagIndex = 2) as RAL_ACT,
    (SELECT Val WHERE TagIndex = 3) as RAL_CMD,
    (SELECT Val WHERE TagIndex = 4) as PPH_weight
FROM 
    Line_32_floats
GROUP BY
    DateAndTime;

但我得到了错误：

列 'Line_32_floats.TagIndex' 在选择列表中无效，因为它不包含在聚合函数或 GROUP BY 子句中。

标签： sqlsql-server

Answer 1

我建议条件聚合：

SELECT DateAndTime as Time, 
       MAX(CASE WHEN TagIndex = 0 THEN Val END) as Normalized_Vac,
       MAX(CASE WHEN TagIndex = 1 THEN Val END) as Avgerage_Vac,
       MAX(CASE WHEN TagIndex = 2 THEN Val END) as RAL_ACT,
       MAX(CASE WHEN TagIndex = 3 THEN Val END) as RAL_CMD,
       MAX(CASE WHEN TagIndex = 4 THEN Val END) as PPH_weight
FROM Line_32_floats f 
GROUP BY DateAndTime;