Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kDfPs-0005hL-Di for pgsql-sql@arkaria.postgresql.org; Thu, 03 Sep 2020 02:56:00 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kDfPn-0002bN-Np for pgsql-sql@arkaria.postgresql.org; Thu, 03 Sep 2020 02:55:55 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kDfPn-0002bF-Fa for pgsql-sql@lists.postgresql.org; Thu, 03 Sep 2020 02:55:55 +0000 Received: from p3plsmtpa09-03.prod.phx3.secureserver.net ([173.201.193.232]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kDfPk-0001d1-4E for pgsql-sql@lists.postgresql.org; Thu, 03 Sep 2020 02:55:54 +0000 Received: from [192.168.1.2] ([152.234.152.71]) by :SMTPAUTH: with ESMTPA id DfPekhq6utiCgDfPgk9bp7; Wed, 02 Sep 2020 19:55:49 -0700 X-CMAE-Analysis: v=2.3 cv=TbHoSiYh c=1 sm=1 tr=0 a=czkgJJsg90Zaaazhdzh3Xw==:117 a=czkgJJsg90Zaaazhdzh3Xw==:17 a=x7bEGLp0ZPQA:10 a=PkefZFhya44A:10 a=epTmVMiNAAAA:8 a=iiGWK4Nmb9i4eBk0lw4A:9 a=CjuIK1q_8ugA:10 a=QCa4FjcAEEoA:10 a=wrRavXeORQ8A:10 a=PCmILGd2EHIMuFvU:21 a=_W_S_7VecoQA:10 a=ndEWmUVY6Yapc0oHF_P4:22 X-SECURESERVER-ACCT: iuri@iurix.com From: Iuri Sampaio Content-Type: multipart/alternative; boundary="Apple-Mail=_34CDE6BA-7F89-4511-AD12-ABA3DD25A96E" Mime-Version: 1.0 (Mac OS X Mail 13.4 \(3608.80.23.2.2\)) Subject: Crossing/Rotating table rows to rows and columns Message-Id: <2B732C2C-5DC9-4D66-A54C-90EFF8780541@gmail.com> Date: Wed, 2 Sep 2020 23:58:46 -0300 To: pgsql-sql X-Mailer: Apple Mail (2.3608.80.23.2.2) X-CMAE-Envelope: MS4wfJZoCmE9HkiF4T+AtXqbMo37nHKUuae68CKga9f5vNECc10N+RXIEl7Lilys00ZC+LNfq//Hazw49qUfNnUZVFHPJM4GmtlhE0c3boVRxBXR7PeSrw4s mjdR0Jpl6/VmGpSjP9tFTlehhqhdd9/lNSBhJu6u8CfbxTecrvnHfCtnsjG0+25VunXoLWDQ2u+ScQ== List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --Apple-Mail=_34CDE6BA-7F89-4511-AD12-ABA3DD25A96E Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=us-ascii Hi there, How would I convert/rotate (i.e. cross table) the following datasource, = which is originally returned by rows (i.e. datetime and total), to rows = as hours and columns as dates, where the columns (dates) will be = assigned with "total" as their value. Here it is the chunk of code to convert from base64url to binary PNG 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 =3D o.object_id AND ci.item_id =3D cr.item_id AND ci.latest_revision =3D cr.revision_id AND ci.content_type =3D :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'' =46rom 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) It would result in the table, as in: 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 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 https://www.postgresql.org/docs/9.2/tablefunc.html = ERROR: function crosstab(unknown, unknown) does not exist LINE 1: select * from crosstab('select o.creation_date::date AS day ... ^ HINT: No function matches the given name and argument types. You might = need to add explicit type casts. Thus, I was wondering if there is a better approach to write a beautiful = code out of it. Does anyone have an idea on how to write this crosstable display? Best wishes, I --Apple-Mail=_34CDE6BA-7F89-4511-AD12-ABA3DD25A96E Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=us-ascii Hi = there,
How would I convert/rotate (i.e. cross table) the = following datasource, which is originally returned by rows (i.e. = datetime and total), to rows as hours and columns as dates, where the = columns (dates) will be assigned with "total" as their value.

Here it is the chunk of code to convert from base64url to = binary PNG

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 =3D o.object_id
AND ci.item_id = =3D cr.item_id
AND ci.latest_revision =3D = cr.revision_id
AND ci.content_type =3D :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''

=46rom 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)

It would result in the table, as = in:

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

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  https://www.postgresql.org/docs/9.2/tablefunc.html

ERROR: function crosstab(unknown, unknown) does not exist
LINE 1: select * from crosstab('select o.creation_date::date = AS day ...
^
HINT: No function matches the = given name and argument types. You might need to add explicit type = casts.



Thus, I was wondering if = there is a better approach to write a beautiful code out of it.

Does anyone have an idea on how to write this crosstable = display?

Best wishes,
I



= --Apple-Mail=_34CDE6BA-7F89-4511-AD12-ABA3DD25A96E--