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
thegroup 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
itto 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
rewordthe query as an outer join. 

HTH,

   Brent Wood


Brent Wood
DBA/GIS consultant
NIWA, Wellington
New Zealand
>>> "zxo102 ouyang" <zxo102@gmail.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.