Window ?

Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Window ?

Olivier Leprêtre

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

 

 


Garanti sans virus. www.avast.com
Reply | Threaded
Open this post in threaded view
|

Re: Window ?

Gerardo Herzig


----- Mensaje original -----
> De: "Olivier Leprêtre" <[hidden email]>
> Para: [hidden email]
> 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

Reply | Threaded
Open this post in threaded view
|

Re: Window ?

David G Johnston
In reply to this post by Olivier Leprêtre
On Wed, Jun 13, 2018 at 7:33 AM, Olivier Leprêtre <[hidden email]> 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.


David J.
Reply | Threaded
Open this post in threaded view
|

RE: Window ?

Olivier Leprêtre

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:[hidden email]]
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 <[hidden email]> 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.

 

 

David J.


Garanti sans virus. www.avast.com
Reply | Threaded
Open this post in threaded view
|

Re: Window ?

Gerardo Herzig


----- Mensaje original -----
> De: "Olivier Leprêtre" <[hidden email]>
> Para: "pgsql-sql" <[hidden email]>
> 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

Next Thread