CODEWITHSUNDEEP
mysqlsqljoinpivotwhere-clause
Jonas Bro
Jonas Bro

Reputation: 63

MySQL query rows to columns

Does anyone know how to make rows into columns while still beign joined?

SELECT p.id
     , f.title 'column'
     , v.value 
  FROM custom_fields f
  JOIN custom_field_values v
    ON v.custom_field_id = f.id
  JOIN projects p
    ON p.id = v.related_to_id
 WHERE p.deleted = 0

y

The rows in column should be the columns and still be grouped by id with value

So it should look something like this:

enter image description here

Upvotes: 0

Views: 54

Answers (1)

GMB
GMB

Reputation: 222592

Use conditional aggregation:

select 
    p.id, 
    max(case when cf.title = 'Rental (FSG)' then cfv.value end) rental_fsg,
    max(case when cf.title = 'Model Year'   then cfv.value end) model_year,
    max(case when cf.title = 'Product'      then cfv.value end) product,
    max(case when cf.title = 'Partner'      then cfv.value end) partner,
    max(case when cf.title = 'SAP delivery / invoice number' then cfv.value end) sap_delivery
from custom_fields cf
join custom_field_values cfv on cfv.custom_field_id = cf.id
join projects p on p.id = cfv.related_to_id
where p.deleted = 0
group by p.id

Note that I modified your query to use table aliases (cf, cfv, p): they make the query easier to write and read.

Upvotes: 2

Related Questions