agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Olivier Leprêtre <o.lepretre@gmail.com>
To: 'pgsql-sql' <pgsql-sql@lists.postgresql.org>
Subject: RE: Window ?
Date: Wed, 13 Jun 2018 17:14:45 +0200
Message-ID: <5b2134ec.1c69fb81.7dd50.7e8f@mx.google.com> (raw)
In-Reply-To: <CAKFQuwY2E1GUmOoNDo-UdxiOSg4gErdpmzDYV5zRJ6JhBO60Uw@mail.gmail.com>
References: <5b212b57.1c69fb81.2e3b4.1d26@mx.google.com>
<CAKFQuwY2E1GUmOoNDo-UdxiOSg4gErdpmzDYV5zRJ6JhBO60Uw@mail.gmail.com>
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 has 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@gmail.com]
Envoyé : mercredi 13 juin 2018 16:55
À : Olivier Leprêtre
Cc : pgsql-sql
Objet : Re: Window ?
On Wed, Jun 13, 2018 at 7:33 AM, Olivier Leprêtre <o.lepretre@gmail.com> wrote:
I want to convert records into lines,
1 att1 att2 att3 att4
2 att5 att6 ...
I 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 null. 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.
---
L'absence de virus dans ce courrier électronique a été vérifiée par le logiciel antivirus Avast.
https://www.avast.com/antivirus
view thread (5+ messages) latest in thread
Message-ID: <5b2134ec.1c69fb81.7dd50.7e8f@mx.google.com>
Permalink: ../5b2134ec.1c69fb81.7dd50.7e8f@mx.google.com/
Also on: postgresql.org/message-id/5b2134ec.1c69fb81.7dd50.7e8f@mx.google.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: o.lepretre@gmail.com, pgsql-sql@lists.postgresql.org
Subject: RE: Window ?
In-Reply-To: <5b2134ec.1c69fb81.7dd50.7e8f@mx.google.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox