Aspen SQLPlus:按名称分隔列

Question

嘿，伙计们，我是SQLplus的新手，目前正在尝试通过各自的标记名将值分隔到各自的列中。我已经能够提取列来过滤掉除请求的标记名称之外的所有标记名称，但我需要让每一列只包含相应的标记，而不是where子句下的每个标记。

Current Iteration

所需输出：

ts                   40FQ8.MV2      40FQ431.MV2
-------------------- -------------- --------------
13-JUN-21 13:49:22.5 4275           4370
13-JUN-21 13:50:22.5 4275           4370
13-JUN-21 13:51:22.5 4275           4370
13-JUN-21 13:52:22.5 4275           4370
13-JUN-21 13:53:22.5 4275           4370
13-JUN-21 13:54:22.5 4275           4370
13-JUN-21 13:55:22.5 4275           4370
13-JUN-21 13:56:22.5 4275           4370
13-JUN-21 13:57:22.5 4275           4370
13-JUN-21 13:58:22.5 4275           4370
13-JUN-21 13:59:22.5 4275           4370
13-JUN-21 14:00:22.5 4275           4370
13-JUN-21 14:01:22.5 4275           4370
13-JUN-21 14:02:22.5 4275           4370
13-JUN-21 14:03:22.5 4275           4370
13-JUN-21 14:04:22.5 4275           4370
13-JUN-21 14:05:22.5 4275           4370
13-JUN-21 14:06:22.5 4275           4370
13-JUN-21 14:07:22.5 4275           4370
13-JUN-21 14:08:22.5 4275           4370
13-JUN-21 14:09:22.5 4275           4370
13-JUN-21 14:10:22.5 4275           4370
13-JUN-21 14:11:22.5 4275           4370

任何帮助都将不胜感激！

Answer 1

如果我正确理解了这个问题，应该是这样的：

select 
  ts,
  case when name = '40FQ8.MV2' then value else null end "40FQ8.MV2",
  case when name = '40FQ431.MV2' then value else null end "40FQ431.MV2"
from history
where name in ('40FQ8.MV2', '40FQ431.MV2')
  and ts between (getdbtime - 14000) and (getdbtime)

Answer 2

因此，经过一些故障排除后，我能够通过利用group by子句和聚合表获得我想要的结果，甚至添加了额外的标签。我不确定这是最优雅的解决方案，但它对我来说很管用:)

SELECT
 ts "Timestamp",
 sum(case when name = '40FQ8.MV2' then max else null end) "40FQ8.MV2",
 sum(case when name = '40FQ431.MV2' then max else null end) "40FQ431.MV2",
 sum(case when name = '40FQ1103.MV2' then max else null end) "40FQ1103.MV2",
 sum(case when name = '40R1-1_MS.PV' then max else null end) "40R1-1_MS.PV",
 sum(case when name = '40R1-2_MS.PV' then max else null end) "40R1-2_MS.PV",
 sum(case when name = '40R1-3MS.PV' then max else null end) "40R1-3MS.PV",
 sum(case when name = '40R1-1PROG.MV2' then max else null end) "40R1-1PROG.MV2",
 sum(case when name = '40R1-2PROG.MV2' then max else null end) "40R1-2PROG.MV2",
 sum(case when name = '40R1-3PROG.MV2' then max else null end) "40R1-3PROG.MV2"
FROM aggregates
WHERE
    ( name = '40FQ8.MV2' OR
      name = '40FQ431.MV2' OR
      name = '40FQ1103.MV2' OR
      name = '40R1-1_MS.PV' OR
      name = '40R1-2_MS.PV' OR
      name = '40R1-3MS.PV' OR
      name = '40R1-1PROG.MV2' OR
      name = '40R1-2PROG.MV2' OR
      name = '40R1-3PROG.MV2' )
 and ts between (getdbtime - 14000) and (getdbtime)
GROUP BY ts
ORDER BY ts

输出：

Timestamp                 40FQ8.MV2    40FQ431.MV2   40FQ1103.MV2   40R1-1_MS.PV   40R1-2_MS.PV    40R1-3MS.PV 40R1-1PROG.MV2 40R1-2PROG.MV2 40R1-3PROG.MV2
-------------------- -------------- -------------- -------------- -------------- -------------- -------------- -------------- -------------- --------------
14-JUN-21 17:04:34.8           4275           4370           4540              6              3              3             30              0              0
14-JUN-21 17:05:34.8           4275           4370           4540              6              3              3             30              0              0
14-JUN-21 17:06:34.8           4275           4370           4540              6              3              3             30              0              0
14-JUN-21 17:07:34.8           4275           4370           4540              6              3              3             30              0              0
14-JUN-21 17:08:34.8           4275           4370           4540              6              3              3             30              0              0
14-JUN-21 17:09:34.8           4275           4370           4540              6              3              3             30              0              0
14-JUN-21 17:10:34.8           4275           4370           4540              6              3              3             30              0              0
14-JUN-21 17:11:34.8           4275           4370           4540              6              3              3             30              0              0
14-JUN-21 17:12:34.8           4275           4370           4540              6              3              3             30              0              0
14-JUN-21 17:13:34.8           4275           4370           4540              6              3              3             30              0              0
14-JUN-21 17:14:34.8           4275           4370           4540              6              3              3             30              0              0
14-JUN-21 17:15:34.8           4275           4370           4540              6              3              3             30              0              0
14-JUN-21 17:16:34.8           4275           4370           4540              6              3              3             30              0              0
14-JUN-21 17:17:34.8           4275           4370           4540              6              3              3             30              0              0
14-JUN-21 17:18:34.8           4275           4370           4540              6              3              3             30              0              0
14-JUN-21 17:19:34.8           4275           4370           4540              6              3              3             30              0              0
14-JUN-21 17:20:34.8           4275           4370           4540              6              3              3             30              0              0
14-JUN-21 17:21:34.8           4275           4370           4540              6              3              3             30              0              0
14-JUN-21 17:22:34.8           4275           4370           4540              6              3              3             30              0              0
14-JUN-21 17:23:34.8           4275           4370           4540              6              3              3             30              0              0
14-JUN-21 17:24:34.8           4275           4370           4540              6              3              3             30              0              0
14-JUN-21 17:25:34.8           4275           4370           4540              6              3              3             30              0              0
14-JUN-21 17:26:34.8           4275           4370           4540              6              3              3             30              0              0