mercredi 6 janvier 2016

SQLite: Sorting data in two columns

Beginner question to SQLite.

I have a table like:

CREATE TABLE raw (ID int, t int, VarID Int,  val float)
INSERT INTO raw VALUES (1, 77 , 1,177)
INSERT INTO raw VALUES (2, 77 , 2,277)
INSERT INTO raw VALUES (3, 12 , 1,112)
INSERT INTO raw VALUES (4, 12 , 2,212)
INSERT INTO raw VALUES (5, 55 , 2,255)

Here tis a timestamp, VarID indicates the to which variable the subsequent val belongs.

Now I want to get an view in which all variables are in separate columns, like:

t   var1   var2
77  177    277
12  112    212
55  None    255

For reference only:

This is the reverse question to Reorganising Data in SQLLIte)

Some code I've tried w/o getting the desired result

CREATE VIEW  view1 
AS SELECT 
t,
(SELECT val FROM raw WHERE VarID=1) as var1,
(SELECT val FROM raw WHERE VarID=2) as var2
FROM raw as r;

Aucun commentaire:

Enregistrer un commentaire