MySQL join on self based in similar fields

Question

Before we begin, don't blame me, I didn't design the database. I am well aware that it is possible to add columns when necessary.

Consider this snippet of mySQL:

SELECT r.id,
   crf.field_name,
   crf.field_value
FROM reports r
   JOIN custom_report_fields crf
     ON crf.report_id = r.id
WHERE r.id IN ( 100, 101 )
   AND field_name LIKE "service_%"  

Which yields these unhelpful results:

ID   | field_name     | field_value
------------------------------------------------
100  | service_id_1   | 20
100  | service_name_1 | "Shipping"
100  | service_cost_1 | 5.25
101  | service_id_1   | 21
101  | service_name_1 | "Handling"
101  | service_cost_1 | 7.50
101  | service_id_2   | 20
101  | service_name_2 | "Shipping"
101  | service_cost_2 | 7.75

I am in need of a query that will yield these results like so:

Report ID | Field ID | Name       | Cost
------------------------------------------------
100       | 20       | "Shipping" | 5.25
101       | 21       | "Handling" | 7.50
101       | 20       | "Shipping" | 7.75

Mind you, there could any number of costs associated with a report, with each successive cost getting another three entries and an increased suffix ('service_id_x', 'service_name_x', 'service_cost_x').

Answerer of this question will receive a far superior afterlife than other followers of your chosen religion.

Answer 1

Try this solution. It's quite clunky(it can only be so elegant given the clunky nature of the design itself!), but it works:

SELECT
    a.id AS Report_ID,
    MAX(CASE WHEN b.field_name LIKE 'service_id_%' THEN b.field_value END) AS Field_ID,
    MAX(CASE WHEN b.field_name LIKE 'service_name_%' THEN b.field_value END) AS Name,
    MAX(CASE WHEN b.field_name LIKE 'service_cost_%' THEN b.field_value END) AS Cost
FROM
    reports a
INNER JOIN
    custom_report_fields b ON a.id = b.report_id
WHERE
    a.id IN (100, 101) AND
    b.field_name LIKE 'service_%'
GROUP BY
    a.id,
    SUBSTRING_INDEX(b.field_name, '_', -1)