CODEWITHSUNDEEP
sqlsql-servertime-seriescrosstable
Adam
Adam

Reputation: 5

Extracting rows as columns from batched time series table

Is it possible to convert table with batched time data series that looks like this:

id technology time value
1 technology 1 123 2
2 technology 2 123 3
3 technology 3 123 0.5
4 technology 1 124 3
5 technology 2 124 1,2
6 technology 3 124 8

to table with particular "technology part" as a dimension (column?), for example this:

id technology time value technology 2
1 technology 1 123 2 3
2 technology 3 123 0.5 3
4 technology 1 124 3 1,2
5 technology 3 124 8 1,2

The value of "technology 2" needs to be in all rows with the same time mark.

Thanks.

I tried to do this in qliksense with "crosstable" function but I'm not able to get the right result, so maybe it will be possible in native SQL.

Upvotes: 0

Views: 63

Answers (2)

SelVazi
SelVazi

Reputation: 16063

This is a way to do it using MAX window function, and CASE expression to build conditional aggregation :

SELECT *
FROM (
  SELECT *, max(case when technology = 'technology 2' then value end )
          over (partition by time) as 'technology 2'
  FROM mytable AS t1
) AS s
WHERE s.technology != 'technology 2';

Demo here

Upvotes: 1

nvogel
nvogel

Reputation: 25534

You could do something like the following.

CREATE TABLE foo (
   id         INTEGER  NOT NULL PRIMARY KEY 
  ,technology VARCHAR(20)  NOT NULL
  ,time       INTEGER  NOT NULL
  ,value      VARCHAR(3) NOT NULL
);

INSERT INTO foo (id,technology,time,value) VALUES
 (1,'technology 1',123,'2')
,(2,'technology 2',123,'3')
,(3,'technology 3',123,'0.5')
,(4,'technology 1',124,'3')
,(5,'technology 2',124,'1,2')
,(6,'technology 3',124,'8');

SELECT t1.id, t1.technology, t1.time, t2.value AS technology2
FROM foo AS t1
LEFT JOIN foo AS t2
ON t1.time = t2.time AND t2.technology = 'technology 2'
WHERE t1.technology != 'technology 2';

Upvotes: 2

Related Questions