Re: Crossing/Rotating table rows to rows and columns

Indeed, tablefunc -> crosstab would be a solution to it. However, the number and label of columns dynamically change depending on the records returned in the creation_date range, declared in the WHERE clasue.

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 ORDER BY datetime ASC'

Furthermore, how would datetime column (o.creation_date) be split into rows and columns as in:

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

So far, I tried to simplify the query to actually get an idea of the target pivot table, removing datetime interval conditionals. Unfortunately it returns an error.
ERROR: return and sql tuple descriptions are incompatible

SELECT * FROM CROSSTAB('select EXTRACT(hour FROM o.creation_date)::text AS hour, o.creation_date::date::text AS day, COUNT(1) 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 = ''qt_face'' GROUP BY o.creation_date, 1 ORDER BY hour ASC') AS t ("hour" TEXT, "day" NUMERIC);
ERROR: return and sql tuple descriptions are incompatible

> On Muh. 14, 1442 AH, at 23:58, Christophe Pettus <xof(at)thebuild(dot)com> wrote:
>
>
>
>> On Sep 2, 2020, at 19:58, Iuri Sampaio <iuri(dot)sampaio(at)gmail(dot)com> wrote:
>> I've tried to use crosstabN(text sql), to solve the problem directly in the datasource layer, but apparently tablefunc is not supported in the datamodel Squema
>
> "tablefunc" is an extension, so you will need to create it in your database before using it:
>
> CREATE EXTENSION tablefunc;
>
> --
> -- Christophe Pettus
> xof(at)thebuild(dot)com
>