Aspen SQLPlus: Separating Columns by Name

Question

Hey guys I'm fairly new to SQLplus and currently trying to separate values into their own column by their respective tag name. I've been able to pull columns that filter out every tag name except the ones requested, but I need to have each column only include the respective tag and not every tag under the where clause.

Current Iteration

Desired Output:

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

Any help would be appreciated!

Answer 1

If I correctly understood the question, that would be something like this:

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

So after some troubleshooting I was able to get the results I was looking for by utilizing group by clause and aggregates table and even added additional tags. I'm not sure its the most elegant solution but it does the trick for me :)

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

Output:

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