MySQL Use dynamic row values as column names

Question

I have this set of data (view table) that I built out of multiple tables.

My problem is, I can't display them in a 'horizontal' way. I searched and found several solutions but they're now outdated.

They use pivot table or crosstab query or group_concat().

So what I need as a result, the dynamic BoxName will be a column name and the price will be under each. And then the Route will be the row headers.

My Goal:

I tried manipulating the data via jQuery but I failed so my last resort is to fix the MySQL data so the jQuery display will be easy.

Any help is highly appreciated.

Answer 1

Step #1: Get the column names:

select distinct BoxName from t

For example, this query will return:

BoxName
-----------
Small Box    
Medium Box
Large Box
Regular Box
Jumbo Box

Step #2: Assemble a dynamic query. Now that you know the columns you can prepare the main query as:

select
  Route,
  max(case when BoxName = 'Small Box' then price end) as `Small Box`,
  max(case when BoxName = 'Medium Box' then price end) as `Medium Box`,
  max(case when BoxName = 'Large Box' then price end) as `Large Box`,
  max(case when BoxName = 'Regular Box' then price end) as `Regular Box`,
  max(case when BoxName = 'Jumbo Box' then price end) as `Jumbo Box`
from t
group by Route
order by max(display_order)