CODEWITHSUNDEEP
sqlpivot
Erik Dinges
Erik Dinges

Reputation: 43

Different rows in separate columns SQL

I am working on a SQL statement which gets different lines 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

output 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 is where type = 0 image1 is where type = 1 etc

I've tried it on several ways, but I can't get it right.

Anyone knows how to do this?

Upvotes: 4

Views: 121

Answers (1)

Taryn
Taryn

Reputation: 247700

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

If you do not have a pivot function then you can also use an 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

Upvotes: 1

Related Questions