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

On Thu, Nov 13, 2008 at 06:56:43AM +1300, Brent Wood wrote:
> 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.

It's perfectly possible to use a GROUP BY clause; all rows from one time
period want to be accumulated into a single row.  To get somewhat close
to Brent's query, the OP could do something like:

  SELECT create_on,
    array_accum(CASE channel when 'channel1' THEN data END) AS data1,
    array_accum(CASE channel when 'channel1' THEN unit END) AS unit1,
    array_accum(CASE channel when 'channel2' THEN data END) AS data2,
    array_accum(CASE channel when 'channel2' THEN unit END) AS unit2,
    array_accum(CASE channel when 'channel3' THEN data END) AS data3,
    array_accum(CASE channel when 'channel3' THEN unit END) AS unit3,
    array_accum(CASE channel when 'channel4' THEN data END) AS data4,
    array_accum(CASE channel when 'channel4' THEN unit END) AS unit4
  FROM record_data
  GROUP BY create_on;

If the number of channels were unknown, a possibility would be:

  SELECT create_on, array_accum(channel||' '||data||' '||unit)
  FROM record_data
  GROUP BY create_on;

If this is being used for things outside PG, turning the resulting
arrays into text can make things easier; array_to_string() is good for
this.  More docs are in:

  http://www.postgresql.org/docs/current/static/functions-aggregate.html
  http://www.postgresql.org/docs/current/static/functions-array.html

If you've got a unique constraint on (create_on,channel) then you
could replace the array_accum() aggregate with MIN.  I've also just
realized that PG doesn't come with array_accum by default, you can find
a definition of it here:

  http://www.postgresql.org/docs/current/static/xaggr.html


  Sam