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.

It still works in mysql 8 and if you don't use mysql 8 cte's will not work with prior mysql version, also don't post pictures for data — nbk, Jul 18 '20 at 19:43
You'll can do it two steps: 1) Get the variants, i.e. the columns names using a simple query. 2) Using dynamic SQL you can assemble the query that will produce the table you want. — The Impaler, Jul 18 '20 at 20:49
Consider handling issues of data display in application code — Strawberry, Jul 18 '20 at 21:03
[Why should I not upload images of code/data/errors when asking a question?](https://meta.stackoverflow.com/q/285551/3404097) [mre] — philipxy, Jan 25 '23 at 09:25

score 2 · Accepted Answer · answered Jul 18 '20 at 20:56

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)

MySQL Use dynamic row values as column names

1 Answers1