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