agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedWindow ?
5+ messages / 3 participants
[nested] [flat]
* Window ?
@ 2018-06-13 14:33 Olivier Leprêtre <o.lepretre@gmail.com>
2018-06-13 14:51 ` Re: Window ? Gerardo Herzig <gherzig@fmed.uba.ar>
2018-06-13 14:54 ` Re: Window ? David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 2 replies; 5+ messages in thread
From: Olivier Leprêtre @ 2018-06-13 14:33 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org
Hi,
I have a road segment table with a few attributes for each. For each
segment, I have a road index and a segment index something like :
road seg colA
1 1 att1
1 2 att2
1 3 att3
1 4 att4
2 1 att5
2 2 att6
I want to convert records into lines,
1 att1 att2 att3 att4
2 att5 att6 ...
I was considering using window function, with a partition on road but the
problem is that segment count is different for each road, up to 30. So it's
seems a bit rough to write something like
select nth_value(colA,1), nth_value(colA,2), nth_value(colA,3),
nth_value(colA,4)...
Of course I can write a script with a loop to insert segments one road after
the other, but before going to this, I would appreciate a smarter idea !
Thanks
---
L'absence de virus dans ce courrier électronique a été vérifiée par le logiciel antivirus Avast.
https://www.avast.com/antivirus
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Window ?
2018-06-13 14:33 Window ? Olivier Leprêtre <o.lepretre@gmail.com>
@ 2018-06-13 14:51 ` Gerardo Herzig <gherzig@fmed.uba.ar>
1 sibling, 0 replies; 5+ messages in thread
From: Gerardo Herzig @ 2018-06-13 14:51 UTC (permalink / raw)
To: Olivier Leprêtre <o.lepretre@gmail.com>; +Cc: pgsql-sql@lists.postgresql.org
----- Mensaje original -----
> De: "Olivier Leprêtre" <o.lepretre@gmail.com>
> Para: pgsql-sql@lists.postgresql.org
> Enviados: Miércoles, 13 de Junio 2018 11:33:50
> Asunto: Window ?
> Hi,
>
>
>
> I have a road segment table with a few attributes for each. For each
> segment, I have a road index and a segment index something like :
>
>
>
> road seg colA
>
> 1 1 att1
>
> 1 2 att2
>
> 1 3 att3
>
> 1 4 att4
>
> 2 1 att5
>
> 2 2 att6
>
>
>
> I want to convert records into lines,
>
>
>
> 1 att1 att2 att3 att4
>
> 2 att5 att6 ...
>
>
Looks like a solution with "pivot":
https://www.postgresql.org/docs/current/static/tablefunc.html
Look for "crosstab" functions.
HTH
Gerardo
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Window ?
2018-06-13 14:33 Window ? Olivier Leprêtre <o.lepretre@gmail.com>
@ 2018-06-13 14:54 ` David G. Johnston <david.g.johnston@gmail.com>
2018-06-13 15:14 ` RE: Window ? Olivier Leprêtre <o.lepretre@gmail.com>
1 sibling, 1 reply; 5+ messages in thread
From: David G. Johnston @ 2018-06-13 14:54 UTC (permalink / raw)
To: Olivier Leprêtre <o.lepretre@gmail.com>; +Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* RE: Window ?
2018-06-13 14:33 Window ? Olivier Leprêtre <o.lepretre@gmail.com>
2018-06-13 14:54 ` Re: Window ? David G. Johnston <david.g.johnston@gmail.com>
@ 2018-06-13 15:14 ` Olivier Leprêtre <o.lepretre@gmail.com>
2018-06-13 15:22 ` Re: Window ? Gerardo Herzig <gherzig@fmed.uba.ar>
0 siblings, 1 reply; 5+ messages in thread
From: Olivier Leprêtre @ 2018-06-13 15:14 UTC (permalink / raw)
To: 'pgsql-sql' <pgsql-sql@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Window ?
2018-06-13 14:33 Window ? Olivier Leprêtre <o.lepretre@gmail.com>
2018-06-13 14:54 ` Re: Window ? David G. Johnston <david.g.johnston@gmail.com>
2018-06-13 15:14 ` RE: Window ? Olivier Leprêtre <o.lepretre@gmail.com>
@ 2018-06-13 15:22 ` Gerardo Herzig <gherzig@fmed.uba.ar>
0 siblings, 0 replies; 5+ messages in thread
From: Gerardo Herzig @ 2018-06-13 15:22 UTC (permalink / raw)
To: Olivier Leprêtre <o.lepretre@gmail.com>; +Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
----- Mensaje original -----
> De: "Olivier Leprêtre" <o.lepretre@gmail.com>
> Para: "pgsql-sql" <pgsql-sql@lists.postgresql.org>
> Enviados: Miércoles, 13 de Junio 2018 12:14:45
> Asunto: RE: Window ?
> 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.
>
Look at section 39.1.4. of https://www.postgresql.org/docs/current/static/tablefunc.html
There is a working example that looks pretty close to what you want.
HTH
Gerardo
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2018-06-13 15:22 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-06-13 14:33 Window ? Olivier Leprêtre <o.lepretre@gmail.com>
2018-06-13 14:51 ` Gerardo Herzig <gherzig@fmed.uba.ar>
2018-06-13 14:54 ` David G. Johnston <david.g.johnston@gmail.com>
2018-06-13 15:14 ` Olivier Leprêtre <o.lepretre@gmail.com>
2018-06-13 15:22 ` Gerardo Herzig <gherzig@fmed.uba.ar>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox