amazon redshift - Partial transpose of SQL results -

i have table columns:

id, n, s, e, w 

i can retrieve single row of table select

select id, n, s, e, w {table name} id = 'xxx' 

giving me

id, n, s, e, w 

however, results each id formatted this:

id, n, w, 1 id, n, e, 2 id, s, e, 3 id, s, w, 4 

in case, n & s latitudes, e & w longitudes, , {n, s, e, w} defines square in system. want return point list winding order can draw them later.

my flavor of sql aws redshift.

my table large, execution speed concern. elegant solution?

simplest solution be:

select id, n, w, 1 your_table id = 'xxx' union select id, n, e, 2 your_table id = 'xxx' union select id, s, e, 3 your_table id = 'xxx' union select id, s, w, 4 your_table id = 'xxx'; 

i not sure if redshift supports left join lateral postgres, if can use:

select t.id, x.* your_table t left join lateral (     values  (n, w, 1), (n, e, 2),             (s, e, 3), (s, w, 4) ) x (col1, col2, col3) on true