How to conditionally SELECT multiple columns with MySQL?

Question

Suppose I have 3 columns to choose from: colA, colB and colC.

We have 7 possibilities to SELECT from:

single column: colA | colB | colC
double column: colA colB  | colA colC | colB colC
triple column: colA colB colC

---

I would like to make a query that enables me to pick which columns I want to SELECT. For instance, if I want to pick columns colB and colC, then the SELECT should return

colB colC
---------
 1    4
 2    5
 3    6
---------

---

I already searched for quite some time, and what I encounter most are the CASE and IF Control Flow Functions, but I could not achieve what it's described with them. For example, if I make the following query, the columns that are not activated show up with NULL values (whereas they should not show up):

SET @colB_switch := 1;
SET @colC_switch := 0;

SELECT
    CASE WHEN @colB_switch = 1 THEN colB END,
    CASE WHEN @colC_switch = 1 THEN colC END
FROM my_table

---

For this simple case, I could simple hard code every single possibility, but when we have dozens of columns we have literally thousands of possibilities!

PS: another approach would be pick which columns to not show up, maybe it's easier?

Answer 1

Following @GordonLinoff suggestion, here's one way to conditionally SELECT multiple columns with MySQL:

SET @colA := NULL;
SET @colB := 'colB';
SET @colC := 'colC';

SET @stat := CONCAT('SELECT ', CONCAT_WS(',', @colA, @colB, @colC), ' FROM my_table');

PREPARE stmt1 FROM @stat;
EXECUTE stmt1;

CONCAT_WS() is convenient because if I do not want to SELECT a given column, I just set it to NULL (colA, in the example).