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 ?
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.