MySQL concat() to create column names to be used in a query?

Question

I would like to concatenate column names in a way that the first part of the column name is a string and the second part is a number which is the result of another query.

For example:

SELECT CONCAT('column', mytable.mycolumn) FROM table ...

Can this be done in some way. This way it doesn't give me errors but I don't get the expected result and it seems the concatenation doesn't work.

Answer 1

I previously said that this couldn't be done, but I was wrong. I ended up needing something like this myself so I looked around, and discovered that server-side prepared statements let you build and execute arbitrary SQL statements from strings.

Here is an example I just did to prove the concept:

set @query := (
  select concat(
    "select",
      group_concat(concat("\n  1 as ", column_name) separator ','),
    "\nfrom dual")
  from information_schema.columns
  where table_name = 'columns')
;
prepare s1 from @query
;
execute s1
;
deallocate prepare s1
;

Answer 2

If the number of columns is fixed, then a non-dynamic approach could be:

select 
  case mytable.mycolumn
    when 1 then column1  -- or: when 'a' then columna
    when 2 then column2
    when ...
    else ...
  end as my_semi_dynamic_column
from ...