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!

score 0 · Answer 1 · answered Jun 13 '21 at 17:23

0

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)

answered Jun 13 '21 at 17:23

Littlefoot

131,892
15
35
57

That certainly looks better than what I had tried! [Heres what the SQLplus looks like.](https://imgur.com/a/vlQR5vD) I wonder if there would be a way to have every column (tagname) compute the value off of the same timestamp rather than separating them out into their own respective timestamps. – Claythorne Jun 13 '21 at 17:47

score 0 · Answer 2 · answered Jun 14 '21 at 22:30

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

Aspen SQLPlus: Separating Columns by Name

2 Answers2