SQL – Database table mapper – Insert into table, based on select and joins

This is a life example of the question so you can understand better what we need.

EXAMPLE

We have 3 tables

cars
*id
*description

car_spec
*id
*car_id
*spec_id
*amount

specs
*id
*name

For each car item we want to keep these data:

*id
*description

And 3 ‘spec’ values located in ‘specs’ table, based on ‘car_spec’ table:

doors
pistons
hp

We want to combine all the required data into one table like this.

car_db
*id
*description
*original_car_id
*doors
*pistons
*hp

Sample data

cars table

id | description
1  | 2020 car 1
2  | 2020 car 2
3  | 2020 car 3

car_spec table

id | car_id | spec_id | amount
1  | 1      | 1       | 2
2  | 1      | 2       | 12
3  | 1      | 3       | 550
4  | 2      | 1       | 4
5  | 2      | 2       | 4
6  | 2      | 3       | 250

spec table

id | name
1  | doors
2  | pistons
3  | hp

sample result table

id | description | original_car_id | doors | pistons | hp
1  | 2020 car 1  | 1               | 2     | 12      | 550
2  | 2020 car 2  | 2               | 4     | 4       | 250
3  | 2020 car 3  | 3               | 4     | 8       | 400

What we need

We need to export a new table with the required data.

Can we do this in sql? If not, any suggestions of how can we do it?

Answer

You would typically use conditional aggregation to pivot the specs. The following syntax should work in pretty much all databases:

select c.id, 
    max(case when s.name = 'doors' then cs.amount end) as doors,
    max(case when s.name = 'pistons' then cs.amount end) as pistons,
    max(case when s.name = 'hp' then cs.amount end) as hp
from cars c
inner join car_spec cs on cs.car_id = c.id
inner join specs s on s.id = cs.spec_id
group by c.id