Different rows in separate SQL columns

Question

I am working on an SQL statement that gets different rows from a database into different columns.

DB1:

id | name 1 | Shoe 2 | Jacket 

db2:

 id | type | image 1 | 0 | image_1_1.jpg 1 | 1 | image_1_2.jpg 1 | 2 | image_1_3.jpg 2 | 0 | image_2_1.jpg 

the conclusion should be

 id | name | image0 | image1 | image2 1 | Shoe | image_1_1.jpg | image_1_2.jpg | image_1_3.jpg 2 | Jacket | image_2_1.jpg | | 

image0, where type = 0 image1 is where type = 1, etc.

I tried it in several ways, but I cannot figure it out.

Does anyone know how to do this?

Answer 1

This type of data transformation is known as a bar. Some databases have a pivot function that can convert rows to columns.

If you do not have a summary function, you can also use the aggregate function with a CASE expression to get the result:

 select t1.id, t1.name, max(case when t2.`type` = 0 then t2.image else '' end) Image0, max(case when t2.`type` = 1 then t2.image else '' end) Image1, max(case when t2.`type` = 2 then t2.image else '' end) Image2 from table1 t1 inner join table2 t2 on t1.id = t2.id group by t1.id, t1.name 

See SQL Fiddle with Demo