Extracting rows as columns from batched time series table

Question

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.

Answer 1

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';

Answer 2

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