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

Question

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: https://i.sstatic.net/Y7pBh.png

Here is my code to generate the first 7 columns:

SELECT
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.

O. Jones · Accepted Answer · 2016-09-20 22:29:14Z

2

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.

answered Sep 20, 2016 at 22:29

O. Jones

109k17 gold badges134 silver badges187 bronze badges

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

2 Comments

ad4s Over a year ago

I tried to put an answer, but thank You for better explanation. It is useless to go into that approach

Aaron T Over a year ago

This is what I imagined. Looks like I'll need to convince some people to allow me to restructure the shipment information table. Thanks!

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related