How transpose a table using SQL?

Question

I have a table like this:

id	P	C	A	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 to get a new transposed structure like 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 you can see ,new field names have been extracted from C field in the original table. Is there any way to do this using SQL?

Answer 1

Postgres has some advanced functionality in terms of arrays and crosstab. However, a database independent way of doing this is by using aggregation:

select t.p,
       max(case when c = 3 then a end) as a3,
       max(case when c = 3 then b end) as b3,
       max(case when c = 4 then a end) as a4,
       max(case when c = 4 then b end) as b4
from atable t
group by t.p;

This will work in both SQLite and Postgres (and just about any other database).