Re: Crossing/Rotating table rows to rows and columns

6:00:00 2 2 4 22 7 4
7:00:00 8 2 3 8 1
8:00:00 3 8 4 1 9 4
9:00:00 4 6 2 35 8
10:00:00 9 19 14 2 10 2
11:00:00 11 8 7 13 10 13 10
12:00:00 12 7 18 12 8 12 5
13:00:00 6 14 8 24 10 6 6

6:00:00 2 2 4 22 7 4
7:00:00 8 2 3 8 1
8:00:00 3 8 4 1 9 4
9:00:00 4 6 2 35 8
10:00:00 9 19 14 2 10 2
11:00:00 11 8 7 13 10 13 10
12:00:00 12 7 18 12 8 12 5
13:00:00 6 14 8 24 10 6 6

hour 2020-7-26 2020-7-27 ... 2020-7-31 2020-8-01
0:00:00
1:00:00
2:00:00
3:00:00
4:00:00
5:00:00 1
6:00:00 2 2 4 22 7 4
7:00:00 8 2 3 8 1
8:00:00 3 8 4 1 9 4
9:00:00 4 6 2 35 8
10:00:00 9 19 14 2 10 2
11:00:00 11 8 7 13 10 13 10
12:00:00 12 7 18 12 8 12 5
13:00:00 6 14 8 24 10 6 6
14:00:00 8 10 9 7 14 11 4
15:00:00 21 10 4 2 13 15
16:00:00 12 15 11 10 22 22
17:00:00 30 14 11 28 10 29
18:00:00 1
19:00:00
20:00:00
21:00:00
22:00:00
23:00:00

On Muh. 15, 1442 AH, at 21:22, Christophe Pettus <xof@thebuild.com> wrote:

On Sep 3, 2020, at 17:20, Iuri Sampaio <iuri.sampaio@gmail.com> wrote:

From the table structure, such as:

hour | total
------------------------+-------
2020-07-26 02:00:00+00 | 1
2020-07-26 04:00:00+00 | 7
2020-07-26 05:00:00+00 | 6
2020-07-26 06:00:00+00 | 6
2020-07-26 07:00:00+00 | 17
2020-07-26 08:00:00+00 | 17
2020-07-26 09:00:00+00 | 6
2020-07-26 10:00:00+00 | 8
2020-07-26 11:00:00+00 | 14
2020-07-26 12:00:00+00 | 16
2020-07-26 13:00:00+00 | 10
2020-07-26 14:00:00+00 | 17
2020-07-26 15:00:00+00 | 15
2020-07-26 16:00:00+00 | 2
2020-07-27 00:00:00+00 | 1
2020-07-27 06:00:00+00 | 1
..
2020-08-01 07:00:00+00 | 7
2020-08-01 08:00:00+00 | 4
2020-08-01 09:00:00+00 | 7
2020-08-01 10:00:00+00 | 10
2020-08-01 11:00:00+00 | 20
2020-08-01 12:00:00+00 | 25
2020-08-01 13:00:00+00 | 18
2020-08-01 14:00:00+00 | 14
2020-08-01 15:00:00+00 | 12
2020-08-01 16:00:00+00 | 4
(91 rows)

to the target pivot table:
hour 2020-7-26 2020-7-27 ... 2020-7-31 2020-8-01
0:00:00
1:00:00
2:00:00
3:00:00
4:00:00
5:00:00 1
6:00:00 2 2 4 22 7 4
7:00:00 8 2 3 8 1
8:00:00 3 8 4 1 9 4
9:00:00 4 6 2 35 8
10:00:00 9 19 14 2 10 2
11:00:00 11 8 7 13 10 13 10
12:00:00 12 7 18 12 8 12 5
13:00:00 6 14 8 24 10 6 6
14:00:00 8 10 9 7 14 11 4
15:00:00 21 10 4 2 13 15
16:00:00 12 15 11 10 22 22
17:00:00 30 14 11 28 10 29
18:00:00 1
19:00:00
20:00:00
21:00:00
22:00:00
23:00:00

You can do that particular type of operation with aggregates:

SELECT hour, array_agg(total) as totals FROM (select date_trunc('hour', o.creation_date) AS datetime,
           COUNT(1) AS total
           FROM cr_items ci, acs_objects o, cr_revisions cr
           WHERE ci.item_id = o.object_id
           AND ci.item_id = cr.item_id
           AND ci.latest_revision = cr.revision_id
           AND ci.content_type = :content_type
           AND o.creation_date BETWEEN :creation_date::date - INTERVAL '6 day' AND :creation_date::date + INTERVAL '1 day'
           GROUP BY 1) t;

This will not include any of the "hour"s that do not have a "total", but you can achieve that with a left outer join against a subquery that uses generate_series to create a single-row table with all of the desired hours in it.

--
-- Christophe Pettus
  xof@thebuild.com