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
– nbk, Commented Jul 18, 2020 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
– The Impaler, Commented Jul 18, 2020 at 20:49
Consider handling issues of data display in application code — Strawberry
– Strawberry, Commented Jul 18, 2020 at 21:03
Why should I not upload images of code/data/errors when asking a question? minimal reproducible example — philipxy
– philipxy, Commented Jan 25, 2023 at 9:25

The Impaler · Accepted Answer · 2020-07-18 20:56:45Z

2

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)

answered Jul 18, 2020 at 20:56

The Impaler

49.3k10 gold badges50 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL Use dynamic row values as column names

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related