postgresql - How transpose a table using SQL? -

postgresql - How transpose a table using SQL? -

i have table this:

id | p | c | | b -------------------- 1 |100 |3 |a1 | b1 2 |101 |3 |a2 | b2 3 |102 |3 |a3 | b3 4 |103 |3 |a4 | b4 5 |100 |4 |a5 | b5 6 |101 |4 |a6 | b6 7 |102 |4 |a7 | b7 8 |103 |4 |a8 | b8

i want new transposed construction this:

p |_3a | _3b |_4a | _4b ------------------------- 100 | a1 | b1 | a5 | b5 101 | a2 | b2 | a6 | b6 102 | a3 | b3 | a7 | b7 103 | a4 | b4 | a8 | b8

as can see ,new field names have been extracted c field in original table. there way using sql?

postgres has advanced functionality in terms of arrays , crosstab. however, database independent way of doing using aggregation:

select t.p, max(case when c = 3 end) a3, max(case when c = 3 b end) b3, max(case when c = 4 end) a4, max(case when c = 4 b end) b4 atable t grouping t.p;

this work in both sqlite , postgres (and other database).

sql postgresql transpose