Crosstable query

Hello,

I'm writing this bit because it adresses a common problem, but uses a
different solution.

In a simple data model of timeseries, we have a table with fields v (for
variable), t (for time) and y (the data value). v,t is the primary key.

It is sometimes desireable to have a side-by-side comparison of variables
for the same timestamp.

so instead of a table

t    v    y
---------------
1    a    0.5
1    b    1.1
1    c    5.1
2    a    0.6
2    b    1.2
2    c    5.2
3    a    0.7
3    b    1.4
3    c    5.5

we would like a table

t    ya    yb    yc
---------------------
1    0.5    1.1    5.1
2    0.6    1.2    5.2
3    0.7    1.4    5.5


MS Access has a 'TRANSFORM' function to do this, which transposes the data
along a pivot.

The solution for PostgreSQL and other databases is to use a so-called
self-join; this means that the table is left joined on subqueries of itself,
using t as the join field. Each subquery selects a different variable.

My solution uses another capability of the database engine: group by and
aggregation.

I started with the building block

case when v = a then y else null end as ya.

This selects only the y of records of a, and null otherwise. Using this on
the above table I would use


select t, case when v = a then y else null end as ya,case when v = b then y else null end as yb,case when v = c then y
elsenull end as yc
 
from thetable;

This yields

t    ya    yb    yc
---------------------
1    0.5
1        1.1    
1            5.1
2    0.6    
2        1.2
2            5.2
3    0.7    
3        1.4
3            5.5

which is almost (but not quite) what we want. The final step is to use the
group by construct. You can use min, max or avg for aggregation of the ya's,
although it is probably best to create a special aggregation function
'first' or something similar.

The final query looks like:

select t, min(case when v = a then y else null end) as ya,min(case when v = b then y else null end) as yb,min(case when
v= c then y else null end) as yc
 
from thetable
group by t;


I did a small performance test of my solution against the self-join method,
which proved to be a big win. But I did little to optimise either query, and
my test set is too small to be reliable. I used version 7.3.4. See for
yourself.


Regards,

Dennis van Dok