sql - separate row by groups and flatten column information -

sql - separate row by groups and flatten column information -

the problem setting

i have table

id type x y 1 type1 1.0 2.0 2 type2 1.2 2.3 3 type1 1.2 2.4

i want separate x,y of type1 , type2 following:

id x_type1 y_type1 x_type2 y_type2 1 1.0 2.0 2 1.2 2.3 3 1.2 2.4

how can implement in postgresql ?

i utilize conditional aggregation:

select t.id, max(case when type = 'type1' x end) x_type1, max(case when type = 'type1' y end) y_type1, max(case when type = 'type2' x end) x_type2, max(case when type = 'type3' y end) y_type2 table t grouping t.id;

sql postgresql pivot