MySQL concat() to create column names to be used in a query?
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
;
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 ...
I don't believe you can do this with CONCAT()
and CONCAT_WS()
. I'd recommend using the langauge you are working with the create the field names. Doing it this way would be pretty scary, depending on where the data in the database came from.