CODEWITHSUNDEEP
sqlpostgresql
amir hodaee
amir hodaee

Reputation: 47

something like pivot table on postgres

I have two tables as below:

car table:

id name
1 BMW
2 Volvo
3 Toyota

option table:

id carid name value
1 1 airbag 1
2 1 seat 4
3 1 sunroof 1
4 2 airbag 0
5 2 seat 4
6 2 sunroof 1
7 3 airbag 0
8 3 seat 5
9 3 sunroof 0

how can I create the below table:

id name airbag sunroof seat
1 BMW 1 1 4
2 Volvo 0 1 4
3 Toyota 0 0 5

Upvotes: 0

Views: 25

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522797

You may use pivoting logic here:

SELECT
    c.id,
    c.name,
    MAX(o.value) FILTER (WHERE o.name = 'airbag')  AS airbag,
    MAX(o.value) FILTER (WHERE o.name = 'sunroof') AS sunroof,
    MAX(o.value) FILTER (WHERE o.name = 'seat')    AS seat
FROM car c
LEFT JOIN option o
    ON o.carid = c.id
GROUP BY
    c.id;

With the help of Postgres' support for the FILTER clause, the pivoting logic above becomes very terse and manageable.

Upvotes: 2

Related Questions