Define column as variable

Question

CREATE TABLE sales (
    id int auto_increment primary key,
    customer VARCHAR(255),
    product VARCHAR(255),
    sales_quantity INT
);

INSERT INTO sales
(customer, product, sales_quantity)
VALUES 
("Customer_A", "Product_01", "100"),
("Customer_B", "Product_03", "970"),
("Customer_B", "Product_04", "730"),
("Customer_C", "Product_02", "480"),
("Customer_D", "Prodcut_01", "320");

Query:

SET
@selected_column = customer;

SELECT
@selected_column
FROM sales_local
GROUP BY 1;

In the query I want to have the flexiblity to change between the columns customer and product.
Therefore, my idea was to declare them as variable.
However, with this I get error Unknown column 'customer' in 'field list'.

Is it possible to declare a column as variable or is there any other similar solution for it?

I know in the simple table above I could achieve this by just changing customer to product within the SELECT part.
However, in my orignal file I use multiple SELECT statements that are binded with UNION ALL and I do not want to change the column name in each of those statement.

Or this? https://stackoverflow.com/questions/53949624/mysql-select-query-with-variable-column-name — Nico Haase, Dec 18 '20 at 08:55
Or this? https://stackoverflow.com/questions/20019484/mysql-how-to-use-a-variable-as-column-name — Nico Haase, Dec 18 '20 at 08:55
Or this? https://stackoverflow.com/questions/29112609/how-to-use-a-variable-as-a-column-name-for-a-select-statement — Nico Haase, Dec 18 '20 at 08:56

score 0 · Answer 1 · answered Dec 18 '20 at 08:51

0

You really need dynamic SQL for something like this. Using MySQL prepared statements, we can try:

SET @selected_column = 'customer';
PREPARE stmt FROM CONCAT_WS(' ', 'SELECT', @selected_column, 'FROM sales_local GROUP BY 1')
EXECUTE stmt;

answered Dec 18 '20 at 08:51

Tim Biegeleisen

502,043
27
286
360

`@selected_column` => `sys.quote_identifier(@selected_column)` At least lets try to protect it a bit against SQL Injection – Lukasz Szozda Dec 18 '20 at 08:53
@LukaszSzozda This isn't something which would be touched from outside Workbench or the command line. If the OP really wants dynamic columns, they should have separate statements for the various queries then want to run. – Tim Biegeleisen Dec 18 '20 at 08:54

Define column as variable

1 Answers1