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("
  1 as ", column_name) separator ','),
    "
from dual")
  from information_schema.columns
  where table_name = 'columns')
;
prepare s1 from @query
;
execute s1
;
deallocate prepare s1
;