在 SQL Server 中转置具有日期时间记录的表

debugcn 发表于 Dev

艺术

我希望在下面转置一个临时表（Result列是DATETIME数据类型）以根据MeasureDSC值聚合记录（创建两个新列：First和Next）。

我正在使用 Microsoft SQL Server 2017 (v14.0.17224.0) 和 Management Studio。

当前表：

+-----------+----------+------------------+------------------+---------+------------+
|    vID    |   fID    |      RESULT      |     Recorded     | LineNBR | MeasureDSC |
+-----------+----------+------------------+------------------+---------+------------+
| 292837518 | 75174227 | 2018-01-01 12:53 | 2018-01-01 9:48  |       1 | First      |
| 292837518 | 75174227 | 2018-01-01 12:54 | 2018-01-01 9:48  |       1 | Next       |
| 295376471 | 76107803 | 2018-01-01 22:51 | 2018-01-01 23:03 |       1 | First      |
| 295376471 | 76107803 | 2018-01-01 22:51 | 2018-01-01 23:03 |       1 | Next       |
| 301032810 | 78252847 | 2018-01-01 13:24 | 2018-01-01 13:45 |       1 | First      |
| 301032810 | 78252847 | NULL             | 2018-01-01 13:45 |       1 | Next       |
| 301867286 | 78566265 | 2018-01-01 14:40 | 2018-01-01 15:00 |       1 | First      |
| 301867286 | 78566265 | 2018-01-01 14:50 | 2018-01-01 15:00 |       1 | Next       |
| 302791918 | 78917501 | 2018-01-01 15:01 | 2018-01-01 7:30  |       1 | First      |
| 302791918 | 78917501 | 2018-01-01 15:05 | 2018-01-01 7:30  |       1 | Next       |
| 304444538 | 79538091 | 2018-01-01 8:00  | 2018-01-01 8:00  |       1 | First      |
| 304444538 | 79538091 | 2018-01-01 8:04  | 2018-01-01 8:00  |       1 | Next       |
| 304478684 | 79550758 | 2018-01-01 14:30 | 2018-01-01 17:44 |       1 | First      |
| 304478684 | 79550758 | 2018-01-01 15:30 | 2018-01-01 17:44 |       1 | Next       |
+-----------+----------+------------------+------------------+---------+------------+

预期的输出应该是这样的：

+-----------+----------+------------------+------------------+------------------+
|    vID    |   fID    |     Recorded     |      First       |       Next       |
+-----------+----------+------------------+------------------+------------------+
| 292837518 | 75174227 | 2018-01-01 9:48  | 2018-01-01 12:53 | 2018-01-01 12:54 |
| 295376471 | 76107803 | 2018-01-01 23:03 | 2018-01-01 22:51 | 2018-01-01 22:51 |
| 301032810 | 78252847 | 2018-01-01 13:45 | 2018-01-01 13:24 | NULL             |
| 301867286 | 78566265 | 2018-01-01 15:00 | 2018-01-01 14:40 | 2018-01-01 14:50 |
| 302791918 | 78917501 | 2018-01-01 7:30  | 2018-01-01 15:01 | 2018-01-01 15:05 |
| 304444538 | 79538091 | 2018-01-01 8:00  | 2018-01-01 8:00  | 2018-01-01 8:04  |
| 304478684 | 79550758 | 2018-01-01 17:44 | 2018-01-01 14:30 | 2018-01-01 15:30 |
+-----------+----------+------------------+------------------+------------------+

我试图使用 PIVOT 函数，但一直收到错误消息：“无效的列名‘RESULT’。”

我的代码：

SELECT 
    vID, fID,
    RESULT,
    Recorded,
    [First] AS [First],
    [Next] AS [Next]
FROM 
    (SELECT 
         vID, fID, RESULT, Recorded, MeasureDSC
     FROM 
         #temp) x
PIVOT 
    (MAX(RESULT) FOR MeasureDSC IN ([First], [Next])) p

将不胜感激任何帮助！

约格什·夏尔马

为什么不直接进行聚合：

SELECT vID, fID, Recorded,
       MAX(CASE WHEN MeasureDSC = 'First' THEN RESULT END) AS First,       
       MAX(CASE WHEN MeasureDSC = 'Next' THEN RESULT  END) AS Next
FROM #temp 
GROUP BY vID, fID, Recorded;

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。