Display data from multiple rows in a single row with multiple columns

Question

I have a PostgreSQL database of some records with timestamps.

Is there simple way to collect data in window during some time (e.g. one month, using PRECEDING and FOLLOWING expressions) and then create multiple columns (e.g. 30 for each day in month) which contain all records during this month?

Let's consider this simple case: a table of two columns timestamp and measurement. What I want is to gain table of 31 columns: timestamp and measurement1, measurement2 ... measurement30, where measurement_i is measurement i days before timestamp.

Window functions operates on the “windows”. What is the definition of the window in your case? I would say _this is the real problem_ to solve here. — vyegorov, Feb 19 '13 at 12:55
Window is quite easy: `WINDOW w AS ( ORDER BY timestamp RANGE BETWEEN '30 days'::interval PRECEDING AND '0 days'::interval FOLLOWING )` — ElectricHedgehog, Feb 19 '13 at 13:37

score 1 · Answer 1 · edited May 23 '17 at 10:32

If you want multiple rows like that in the result, I would use a crosstab() function.

But according to your description you want a single row. I would solve that with the window functions lead() or lag().
The major trick is to use a subquery or CTE to generate all columns before you apply your WHERE clause and narrow the result down to a single row. I would use a CTE here:

Given the following table (which you should have provided):

CREATE TABLE tbl(
  tbl_id serial PRIMARY KEY
 ,ts timestamp NOT NULL
 ,value int
);

One row per day guaranteed

The query could look like this:

WITH x AS (
   SELECT tbl_id, ts, value
         ,lag(value, 1) OVER w AS value_day_before_1
         ,lag(value, 2) OVER w AS value_day_before_2
   --    ...
         ,lead(value, 1) OVER w AS value_day_after_1
         ,lead(value, 2) OVER w AS value_day_after_2
   --    ...
   FROM   tbl
   WINDOW w AS (ORDER BY ts) 
   )
SELECT *
FROM   x
WHERE  ts = '2013-02-14 0:0'::timestamp

At most one row per day, but days can be missing:

Also timestamp can be any time during the day.

Generate a list of days with generate_series() and LEFT JOIN your table to it:

WITH x AS (
   SELECT tbl_id, ts, value
         ,lag(value, 1) OVER w AS value_day_before_1
         ,lag(value, 2) OVER w AS value_day_before_2
   --    ...
         ,lead(value, 1) OVER w AS value_day_after_1
         ,lead(value, 2) OVER w AS value_day_after_2
   --    ...
   FROM   (
       SELECT generate_series ('2013-02-01'::date
                              ,'2013-02-28'::date
                              ,interval '1d') AS day
       ) d
   LEFT JOIN tbl t ON date_trunc('day', t.ts) = d.day
   WINDOW w AS (ORDER BY day) 
   )
SELECT *
FROM   x
WHERE  ts = '2013-02-14 0:0'::timestamp;

->sqlfiddle

Display data from multiple rows in a single row with multiple columns

1 Answers1

One row per day guaranteed

At most one row per day, but days can be missing: