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 1mUAiG-0005dy-NQ for pgsql-sql@arkaria.postgresql.org; Sat, 25 Sep 2021 16:39:45 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1mUAiF-0007xH-Bt for pgsql-sql@arkaria.postgresql.org; Sat, 25 Sep 2021 16:39:43 +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 1mUAiF-0007x8-0z for pgsql-sql@lists.postgresql.org; Sat, 25 Sep 2021 16:39:43 +0000 Received: from p3plsmtpa12-01.prod.phx3.secureserver.net ([68.178.252.230]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1mUAiC-0006ve-Dp for pgsql-sql@lists.postgresql.org; Sat, 25 Sep 2021 16:39:42 +0000 Received: from smtpclient.apple ([186.241.25.244]) by :SMTPAUTH: with ESMTPSA id UAi7mtUcd01BGUAi8mIyMa; Sat, 25 Sep 2021 09:39:37 -0700 X-CMAE-Analysis: v=2.4 cv=Vpzmv86n c=1 sm=1 tr=0 ts=614f50c9 a=KW09giZqzVun2h+F11QPXw==:117 a=KW09giZqzVun2h+F11QPXw==:17 a=x7bEGLp0ZPQA:10 a=PkefZFhya44A:10 a=XOV-8aRKEwwGzJRP4mMA:9 a=QEXdDO2ut3YA:10 a=18shIMmw_i_gv888gLYA:9 a=A9OYntglVtrRR37x:21 a=_W_S_7VecoQA:10 X-SECURESERVER-ACCT: iuri@iurix.com From: Iuri Sampaio Content-Type: multipart/alternative; boundary="Apple-Mail=_AFCF6DC2-DDBB-4A70-8753-13BCC1C614BC" Mime-Version: 1.0 (Mac OS X Mail 14.0 \(3654.100.0.2.22\)) Subject: Creating a query to structure results grouped by two columns, pivoting only a third colum Message-Id: <4A8701B1-72AA-408F-9A7D-8DDC358E8AB3@gmail.com> Date: Sat, 25 Sep 2021 13:39:34 -0300 To: pgsql-sql@lists.postgresql.org X-Mailer: Apple Mail (2.3654.100.0.2.22) X-CMAE-Envelope: MS4xfGiyzbyKhaD1sy+mtQSTq3RDTaJQkk9spIyGa5UiuG4o903Cz/lMgRefl1CNT4sEr3bBq1Vii1uwljy6ax2T4KTYoxX46IJqVvEcHuq40A739HPWpvLV rTa/mwaEFK1whgXfJci3LjBcUfSXmutNZ6TpyGdBR3IZMsuozoJ4fP0GCEnEosi6YaLbmMZqw9RJV68r9cHYX1etITMKzGjqR78= List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_AFCF6DC2-DDBB-4A70-8753-13BCC1C614BC Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 Hello there, In order to achieve such structure "pivoting" table and "grouping by" = multiple columns. (i.e. as illustrated below), what would be the SQL = implementation? The source query is: ```` SELECT=20 t1.date,=20 t1.area,=20 t1.canal, SUM(t1.peso) AS peso FROM table1 t1 GROUP BY 1, 2, 3 ORDER BY 1, 2, 3=20 ```` and source query generates a initial structure as in: ########################### date | area | canal = | peso 2021-03-01 area1 can1 = 45.6768 2021-03-01 area1 can2 = 54.6768 2021-03-01 area1 can3 = 87.6 2021-03-01 area2 can1 = 1.87 2021-03-01 area2 can2 = 12.7687 2021-03-01 area2 can3 = 965.568 2021-03-01 area3 can1 = 968.95 2021-03-01 area3 can2 = 1.6867 2021-03-01 area3 can3 = 8.897 =E2=80=A6. =E2=80=A6 = =E2=80=A6 ... 2021-06-01 area1 can1 2021-06-01 area1 can2 2021-06-01 area1 can3 2021-06-01 area2 can1 =E2=80=A6 =E2=80=A6 = ... 2021-12-01 area1 2021-12-01 area1 2021-12-01 area1 ########################### Then, the goal's to achieve a final structure grouped by columns "area" = and "canal", pivoting column =E2=80=9Cdate", but only to the column = "peso". Plus, a partial total of each area, named as "total=E2=80=9D . I tried to write a query to support such a display, however, I got stuck = at pivoting date only to the column peso ```` SELECT =20 t2.area, t2.canal, ( SELECT=20 month, peso_valor FROM ( SELECT=20 month(t1.date) month,=20 t1.area,=20 t1.canal, SUM(t1.peso_valor) AS peso_valor FROM tbl_data t1 GROUP BY 1, 2, 3 ORDER BY 1, 2, 3 ) source_table=20 ) PIVOT ( =20 peso FOR month IN ( 1 Jan, 2 Feb, 3 Mar, 4 Apr, 5 Mai, 6 Jun, 7 Jul, = 8 Aug, 9 Sep, 10 Oct, 11 Nov, 12 Dec ) =20 ) as pivot_table ORDER BY month ) as t2.peso=20 FROM tbl_data t2 GROUP BY 1, 2 ```` what would be the SQL implementation to achieve a structure as such ? =20= (.i.e. grouped by columns "area" and "canal", pivoting column =E2=80=9Cda= te", but only to the column "peso=E2=80=9D.) illustrated bellow? Best wishes, I ########################### area | canal | 2021-03-01 = | 2021-04-01 | = 2021-05-01 | 2021-06-01 | ... peso = peso = peso peso area1 can1 45.6768 = 875.98 = 1.232 =E2=80=A6=09 area1 can2 54.6768 = 665.8 = 2.43 ... area1 can3 87.6 = 65.8 = 4.76 ... area1 total = SUM(45.6768+54.6768+87.6) SUM(875.98+665.8+65.8) = SUM(1.232+2.43+4.76) ... area2 can1 1.87 = =E2=80=A6 = ... area2 can2 12.7687 area2 can3 965.568 area2 total = SUM(1.87+12.7687+965.568) =E2=80=A6 = ... area3 can1 968.95 area3 can2 1.6867 area3 can3 8.897 ... ########################### --Apple-Mail=_AFCF6DC2-DDBB-4A70-8753-13BCC1C614BC Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8
Hello there,
In order to = achieve such structure "pivoting" table and "grouping by" multiple = columns. (i.e. as illustrated below), what would be the SQL = implementation?
The source query = is:

````
SELECT 
  t1.date, 
  = t1.area, 
  t1.canal,
  SUM(t1.peso) AS peso
FROM table1 t1
GROUP BY 1, 2, 3
ORDER BY 1, 2, = 3 

````

and source query generates a initial structure as = in:

###########################
date = |  area  | = canal = | = peso
2021-03-01 = area1 = can1 45.6768
2021-03-01 = area1 can2 = 54.6768
2021-03-01 = area1 can3 = 87.6
2021-03-01 area2 = can1 = 1.87
2021-03-01 area2 = can2 = 12.7687
2021-03-01 area2 = can3 = 965.568
2021-03-01 area3 = can1 = 968.95
2021-03-01 area3 = can2 = 1.6867
2021-03-01 = area3 = can3 8.897
=E2=80=A6. = =E2=80=A6 = =E2=80=A6 = ...
2021-06-01 = area1 = can1
2021-06-01 = area1 = can2
2021-06-01 = area1 = can3
2021-06-01 = area2 = can1
=E2=80=A6 = =E2=80=A6 = ...
2021-12-01 = area1
2021-12-01 area1
2021-12-01 = area1
###########################


Then, = the goal's to achieve a final structure grouped by columns "area" and = "canal", pivoting column =E2=80=9Cdate", but only to the column = "peso".
Plus, a partial total of each area, named as = "total=E2=80=9D .

I tried to write a query to support such a display, however, = I got stuck at pivoting date only to the column = peso

````
SELECT  
  t2.area,
  = t2.canal,
  ( = SELECT 
month,
= peso_valor
FROM (
  =   = SELECT 
      = month(t1.date) month, 
    =   = t1.area, 
      = = t1.canal,
      = SUM(t1.peso_valor) AS peso_valor
  =   = FROM tbl_data t1
    = GROUP BY 1, 2, 3
    = ORDER BY 1, 2, 3
) = source_table 
) PIVOT (  
  peso
  FOR month IN (
    1 Jan, 2 Feb, 3 = Mar, 4 Apr, 5 Mai, 6 Jun, 7 Jul, 8 Aug, 9 Sep, 10 Oct, 11 Nov, 12 = Dec
  )  
= ) as pivot_table
ORDER BY = month
) as = t2.peso 
FROM tbl_data = t2
GROUP BY 1, 2
````

what = would be the SQL implementation to achieve a structure as such =  ?  
(.i.e.  grouped by columns "area" and "canal", pivoting column = =E2=80=9Cdate", but only to the column = "peso=E2=80=9D.)

 illustrated = bellow?


Best wishes,
I






###########################
= area  |  canal  |  = 2021-03-01  |  = 2021-04-01 = |  2021-05-01 | 2021-06-01 | = ...
= peso peso = peso = peso

= area1 = can1 45.6768 = 875.98 = 1.232 = =E2=80=A6
area1 = can2 54.6768 665.8 = 2.43 = ...
area1 = can3 87.6 = 65.8 = 4.76 = ...
area1 total = SUM(45.6768+54.6768+87.6) = SUM(875.98+665.8+65.8) SUM(1.232+2.43+4.76) = ...

= area2 = can1 1.87 = =E2=80=A6 = ...
= area2 can2 12.7687
area2 = can3 965.568
area2 = total SUM(1.87+12.7687+965.568) = =E2=80=A6 = ...


area3 = can1 968.95
area3 = can2 1.6867
= area3 = can3 8.897

...
###########################

= --Apple-Mail=_AFCF6DC2-DDBB-4A70-8753-13BCC1C614BC--