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.

 

Olivier

De : David G. Johnston [mailto:[email protected]] 
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 <[email protected]> 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