Received: from malur.postgresql.org ([2a02:16a8:dc51::56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1fT7UB-0000xF-6b for pgsql-sql@arkaria.postgresql.org; Wed, 13 Jun 2018 15:14:59 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1fT7UA-0006ng-3S for pgsql-sql@arkaria.postgresql.org; Wed, 13 Jun 2018 15:14:58 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1fT7U9-0006nX-Pj for pgsql-sql@lists.postgresql.org; Wed, 13 Jun 2018 15:14:57 +0000 Received: from mail-wr0-x241.google.com ([2a00:1450:400c:c0c::241]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1fT7U6-00012x-On for pgsql-sql@lists.postgresql.org; Wed, 13 Jun 2018 15:14:57 +0000 Received: by mail-wr0-x241.google.com with SMTP id x4-v6so3150619wro.11 for ; Wed, 13 Jun 2018 08:14:54 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=message-id:from:to:references:in-reply-to:subject:date:mime-version :thread-index:content-language; bh=bzOMXn5r8Wy4fE0Zr5wN/Z6k/4Ua2DAHhyvff1ACAxs=; b=l7f/eQ/tiNr+rjH6zSj7GmRHTz87tG/7aVlt343c7dELdtmW8JXyNCn0EkjtmWbSv4 UYHQlL4Y8SyPb4L9FxrnpjDQ56f/yfz50a0waKYg62lMWn+kfQV/4+UIWy1JDToYyFdN kl4lA9gdYtvFZU3MJn8tyla4lP8tNo6ndAexCRCeApwU20F6bJfObGFzfoc6MlyBhFNS YfEFFyEV0X8Grd0EwWm0qR1PJC1fSvu80pLgEaGdC9h7ICM7jdMyDU+X7YSBd3qChJjr 6j+AH3C6IMPmueU5FGlGq3IN+LphV0kVov+3GdhwF9iHdoAuV3aeb5eY44T8OBpGnmHu SuPA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:message-id:from:to:references:in-reply-to :subject:date:mime-version:thread-index:content-language; bh=bzOMXn5r8Wy4fE0Zr5wN/Z6k/4Ua2DAHhyvff1ACAxs=; b=bU8kFc58o2XOzxM2QtxPtJFDWTGmci09cefvY7rPDUyAW55Fk3W0uGGA1xt2L/jPXc QYGfdrUAvIQqpJ+8IPWH8JJyKSl+veCzVrA94K9j1giCtH80Dh9a0x3L7yXlkm6hXvn3 Q9a3BGbfMAQDL0LtecK6l5L5QQ1wwNUDl4t68uvrSohmSOp4OgbOfgTXNJebXxQ1E1Vd w0UcmMkoplF/qxfwHUKk9ORyw3l7ypDsVu3aCzuykS3V0Ln++mo56I3WpnBWNcopWzKR qGxlNxFt1/Bdfv1ZGFOzqpFl7HxJdKtpTILWwR+sahsHjnWXC7d9WpQq6g+9XCeIePB5 ZyJw== X-Gm-Message-State: APt69E1bHSCQ/E22uJmtqB/Afn/VSadtU6cCwA/JrhCbnD5gobLsVJeg 82LIh8m69Kuo2Ongi1N0K1hjuQ== X-Google-Smtp-Source: ADUXVKJuEFpLxj0VSddS+CDxt6/Q0MJ22qa69Ky2gn/8T9pwbGZv5DblwwA8me2wWNMZUe1ZYt3jyg== X-Received: by 2002:adf:fa92:: with SMTP id h18-v6mr4663633wrr.258.1528902893125; Wed, 13 Jun 2018 08:14:53 -0700 (PDT) Received: from PADOUE ([151.80.244.210]) by smtp.gmail.com with ESMTPSA id v129-v6sm3661318wme.26.2018.06.13.08.14.50 for (version=TLS1 cipher=ECDHE-RSA-AES128-SHA bits=128/128); Wed, 13 Jun 2018 08:14:52 -0700 (PDT) Message-ID: <5b2134ec.1c69fb81.7dd50.7e8f@mx.google.com> X-Google-Original-Message-ID: <009601d40329$446306f0$cd2914d0$@lepretre@gmail.com> From: =?UTF-8?Q?Olivier_Lepr=C3=AAtre?= To: "'pgsql-sql'" References: <5b212b57.1c69fb81.2e3b4.1d26@mx.google.com> In-Reply-To: Subject: RE: Window ? Date: Wed, 13 Jun 2018 17:14:45 +0200 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0097_01D4033A.07EBD6F0" X-Mailer: Microsoft Office Outlook 12.0 Thread-Index: AdQDJnnwKQo8evXaRkq5VKU443DigwAAgyyw Content-Language: fr X-Antivirus: Avast (VPS 180613-2, 13/06/2018), Outbound message X-Antivirus-Status: Clean List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. ------=_NextPart_000_0097_01D4033A.07EBD6F0 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Thanks David, Gerardo, I had a look to crosstab functions but wasn't able to make them work, docum= entation is not precise enough to me, I would appreciate if someone has a w= orking sample. Based on your suggestion, I will try again anyway. The main = difficulty is that I have not only one column but half a dozen taht I would= like to appear road 1 colA colB colC colD colE ColF colA colB colC colD colE ColF colA col= B colC colD colE ColF ... road 2 colA colB... for each road. Olivier De : David G. Johnston [mailto:david.g.johnston@gmail.com] Envoy=C3=A9 : mercredi 13 juin 2018 16:55 =C3=80 : Olivier Lepr=C3=AAtre Cc : pgsql-sql Objet : Re: Window ? On Wed, Jun 13, 2018 at 7:33 AM, Olivier Lepr=C3=AAtre wrote: I want to convert records into lines, 1 att1 att2 att3 att4 2 att5 att6 ... =E2=80=8BI would recommend either an actual array (array_agg function) or a= structured string (string_agg function) SELECT road, array_agg(colA ORDER BY seg) FROM tbl GROUP BY road; Otherwise you will need a output 31 columns with unused columns holding nul= l. You can do that brute-force or you can leverage the tablefunc extension= 's crosstab function. https://www.postgresql.org/docs/10/static/tablefunc.html David J. =E2=80=8B --- L'absence de virus dans ce courrier =C3=A9lectronique a =C3=A9t=C3=A9 v=C3= =A9rifi=C3=A9e par le logiciel antivirus Avast. https://www.avast.com/antivirus ------=_NextPart_000_0097_01D4033A.07EBD6F0 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

Thanks David, Gerardo,

 

I had a look to crosstab functions but wasn't able to make them work,= documentation is not precise enough to me, I would appreciate if someone h= as a working sample. Based on your suggestion, I will try again anyway. The= main difficulty is that I have not only one column but half a dozen taht I= would like to appear

 

road 1 colA colB colC colD colE ColF colA colB colC colD colE ColF colA= colB colC colD colE ColF ...

road 2 colA colB...

 

for each road.

=  

Olivier

De : David G. Johnston [mailto:david.g.johnston@g= mail.com]
Envoy=C3=A9 : mercredi 13 juin 2018 16:55
= =C3=80 : Olivier Lepr=C3=AAtre
Cc : pgsql-sql
Objet : Re: Window ?

 

On Wed, Jun 13, 2018 at 7:33 AM, Olivier Lepr= =C3=AAtre <o.l= epretre@gmail.com> wrote:

 

I want to con= vert records into lines,

 

1        att1&n= bsp;   att2    att3    att4<= o:p>

2        att5=     att6    ...

<= o:p> 

 

=E2=80=8BI would recommend either an actual arr= ay (array_agg function) or a structured string (string_agg function)

 

SELECT road, array_a= gg(colA ORDER BY seg)

= FROM tbl=

GROUP BY road;

 

Otherwise you will need a output 31 columns with unused columns = holding null.  You can do that brute-force or you can leverage the tab= lefunc extension's crosstab function.

 = ;

=
------=_NextPart_000_0097_01D4033A.07EBD6F0--