MySql: adding columns dynamically, as many as rows in another table

Question

Transport table

id  name
1   T1
2   T2

Pallets table

id  name
1   P1
2   P2

Transport Pallet Capacity table

id  transport_id  pallet_id  capacity
1   1             1          10
2   1             2          null
3   2             1          20
4   2             2          24

How to generate table like this:

id  transport_id   pallet_id_1_capacity   pallet_id_2_capacity
1   1              10                     null
2   2              20                     24

Problem: pallets and transports can be added, so, neither quantity is known in advance. For example, manager adds another pallet type and 'pallet_id_3_capacity' column should be generated (and can show null if no capacity data is yet available). Another manager can fill 'transport pallet capacity' table later when notified.

Is there a way to build sql in mysql that will care about the above: specifically - dynamic number of pallets?

Answer 1

The SQL select-list must be fixed at the time you write the query. You can't make SQL that auto-expands its columns based on the data it finds.

But your request is common, it's called a pivot-table or a crosstab table.

The only solution is to do this in multiple steps:

1. Query to discover the distinct pallet ids.
2. Use application code to build a dynamic SQL query with as many columns as distinct pallet id values found in the first query.
3. Run the resulting dynamic SQL query.

This is true for all SQL databases, not just MySQL.

See MySQL pivot row into dynamic number of columns for a highly-voted solution for producing a pivot-table query in MySQL.

I am not voting your question as a duplicate of that question, because your query also involves transport_id, which will make the query solution a bit different. But reading about other pivot-table solutions should get you started.