Aaron T Aaron T - 1 year ago 77
MySQL Question

Selecting a column name dynamically (based on another table row's value)

I have an interesting problem here that I'm not sure is possible to solve without restructuring the database I'm working with, but here goes...

I have a query that needs to select information in the following format: "SKU", "Product Category", "Product Sub Category", "On Hand", "Location", "Allocated", "Available", "Receiving This Month"

I have every column working fine excluding the "Receiving This Month" column, which is a bit complicated. For this column I need to use the SKU value (called fishbowlinventoryavailabilitybylg.part) to get a sum of every row of a column in the "shipments" table where the column has the same name as the SKU.

Here would be an example output that may help: http://i.imgur.com/rv7lXxJ.png

Here is my code to generate the first 7 columns:

fishbowlinventoryavailabilitybylg.part as SKU,
domo_productcosts_xlsx_costs.`product category` AS `Product Category`,
domo_productcosts_xlsx_costs.`product sub category` AS `Product Sub Category`,
fishbowlinventoryavailabilitybylg.qty AS `On Hand`,
fishbowlinventoryavailabilitybylg.LG AS Location,
fishbowlinventoryavailabilitybylg.allocated AS `Allocated`,
fishbowlinventoryavailabilitybylg.qty - fishbowlinventoryavailabilitybylg.allocated AS `Available`,

To try to generate the "Receiving This Month" I have tried a few different things but I've had no luck. The sum part I can do fine, but it's looking up column names dynamically that I can't figure out. I almost wish I could do something like "shipments.fishbowlinventoryavailabilitybylg.part) to get that column in the shipments table, but that makes no sense.

Answer Source

You can't use variables (such as values in a SKU column) as column names in MySQL or most other dialects of SQL. For this reason, most database designers avoid that kind of approach to laying out tables.

Most programmers who need to do such things write programs to generate the SQL queries they need. The built-in table called information_schema.columns can come in hand for that.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download