CODEWITHSUNDEEP
mysqlsql
nish
nish

Reputation: 7280

How to select as different column based on value of a field

I have two tables products and dimensions. dimensions table has following fields: product_id, name, value, unit.

I want to join these tables such that each dimension name appears as a separate column in the result. Eg. I have following entries in dimensions table:

1, width, 4, inches
2, width, 5, inches
1, length, 10, inches
2, height, 7, inches

I want the result to be like:

product id, length, length unit, width, width unit, height, height unit
1             10      inches       4       inches
2                                  5       inches     7       inches

How can I achieve this using sql query?

Upvotes: 0

Views: 54

Answers (1)

newman
newman

Reputation: 2719

You can try use it for to LEFT JOIN statments.

For example

SELECT p.id, l.value AS length, l.unit AS length_unit, w.value AS width, w.unit AS width_unit, h.value AS height, h.unit AS height_unit
FROM products AS p
LEFT JOIN dimensions AS l ON (p.id = l.product_id AND l.name = 'length')
LEFT JOIN dimensions AS w ON (p.id = w.product_id AND w.name = 'width')
LEFT JOIN dimensions AS h ON (p.id = h.product_id AND h.name = 'height')

Upvotes: 3

Related Questions