0

I am supposed to write a SQL Query in cassandra to show few rows as columns. The table looks like this with tag_type_id values ranging from 1,2,3,..42.

+--------+-----------+-------------------+-------+
|asset_id|tag_type_id|           datetime|  value|
+--------+-----------+-------------------+-------+
|       1|         1|2017-07-28 11:25:...|1202.65|
|       1|         1|2017-07-28 11:24:...|1212.17|
|       1|         1|2017-07-28 11:23:...|1214.51|
|       1|         1|2017-07-28 11:21:...|1210.79|
|       1|         1|2017-07-28 11:20:...|1207.01|
|       1|         1|2017-07-28 11:19:...|1208.17|
|       1|         1|2017-07-28 11:18:...| 1205.7|
|       1|         1|2017-07-28 11:16:...|1206.11|
|       1|         1|2017-07-28 11:13:...|1208.53|
|       1|         1|2017-07-28 11:11:...|1207.82|
|       1|         1|2017-07-28 11:10:...|1205.05|
|       1|         1|2017-07-28 11:09:...|1205.56|
|       1|         1|2017-07-28 11:08:...|1204.55|
|       1|         1|2017-07-28 11:06:...|  92.17|
|       1|         1|2017-07-28 11:05:...|1213.93|
|       1|         1|2017-07-28 11:00:...|1205.13|
|       1|         1|2017-07-28 10:59:...|1204.42|
|       1|         1|2017-07-28 10:54:...|1209.42|
|       1|         1|2017-07-28 10:52:...| 1209.6|
|       1|         1|2017-07-28 10:50:...|1213.63|
+--------+----------+--------------------+-------+

I need to see the tag_type_id of values 39 and 40 as column rather than seeing as a row in same table. I want it like this 

asset_id tag_type_id    datetime     value tag_type_id    datetime      value 
1            39       2017-07-28 11:25   90   40      2017-07-28 11:25   0.3
1            39       2017-07-28 11:24   91   40      2017-07-28 11:24   0.9
1            39       2017-07-28 11:23   90   40      2017-07-28 11:23   0.024
1            39       2017-07-28 11:22   89   40      2017-07-28 11:22   0.9
1            39       2017-07-28 11:21   91   40      2017-07-28 11:21   0.25

I tried to do it using pivot like here But it throws error:

select *
from
(
select tag_type_id, datetime, value
from energydata.demodata where asset_id = 1
) src
pivot
(
sum(value)
for tag_type_id in ([39],[40])
) piv;

com.datastax.driver.core.exceptions.SyntaxError: line 3:0 no viable alternative at input '(' (select *from[(]...)
com.datastax.driver.core.exceptions.SyntaxError: line 3:0 no viable alternative at input '(' (select *from[(]...)

what should i do to view table like above?

Thank you.

undefined_variable
  • 6,180
  • 2
  • 22
  • 37
Magg_rs
  • 323
  • 2
  • 3
  • 12

1 Answers1

0
select t1.tag_type_id, t1.datetime, t1.value,
       t2.tag_type_id, t2.datetime, t2.value
from table t1
join table t2 on t1.tag_type = 39 and t2.tag_type = 40 and 
                 t1.datetime = t2.datetime
Radim Bača
  • 10,646
  • 1
  • 19
  • 33