Re: how to "group" several records with same timestamp into one line?

You need to use a self relation, not a group by, as no data are being aggregated into a new single value, which is what the group by achieves.

This joins a table to itself, so that columns in it can be replicated. The key is that the where clause in each case
needs to just select one channel, so it acts like a similar table but contains different rows.

Because you used a char() instead of varchar() for channel, you may find your string 'channel1' has spaces in it to pad it to the specified length, in which case the where clauses below can use "like '%channel1%'" instead of "= 'channel1'"
or you can strip the spaces before the comparison, eg: where "trim(a.channel)='channel1'". I hope this makes sense.

eg: select a.data,
a.unit,
b.data,
b.unit,
c.data,
c.unit,
d.data,
d.unit,
a.create_on
from record data a,
record-data b,
record_data c,
record_data d
where a.channel='channel1'
and b.channel='channel2'
and c.channel='channel3'
and d.channel=channel4
and b.create_on = a.create_on
and c.create_on = a.create_on
and d.create_on = a.create on;

Thus table a comprises all records from channel1, etc... and they are joined on a common timestamp.

NOTE: if any of the channels are null for any given timestamp, you will get no record for that timestamp using this syntax, even if other channels had data, because the query uses an inner join. If this is a problem then you'll need to reword the query as an outer join.

HTH,

Brent Wood

Brent Wood
DBA/GIS consultant
NIWA, Wellington
New Zealand
>>> "zxo102 ouyang" <zxo102(at)gmail(dot)com> 11/13/08 3:15 AM >>>
Hi everyone,
My data with same timestamp "2008-11-12 12:12:12" in postgresql are as
follows

rowid data unit channel create_on
------------------------------------------------------------------------------
1 1.5 MPa channel1 2008-11-12 12:12:12
2 2.5 M3 channel2 2008-11-12 12:12:12
3 3.5 M3 channel3 2008-11-12 12:12:12
4 4.5 t channel4 2008-11-12 12:12:12
------------------------------------------------------------------------------

I would like to "group" them into one line with SQL like

1.5 MPa 2.5 M3 3.5 M3 4.5 t 2008-11-12 12:12:12

The table is defined as

CREATE TABLE record_data
(
rowid serial NOT NULL,
data double precision,
unit character(10),
channel character(20),
create_on timestamp
)

Is that possible? Thanks for your help in advance.

Ouyang

NIWA is the trading name of the National Institute of Water & Atmospheric Research Ltd.