mysql concat() to create column names?

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' work.

UPDATE: It seems it can't be done. I solved it by concatenating in PHP which means I use two queries instead of one. The question of (in)sufficient normalization is legitimate but will be asked another time. Thanks for all the input.

Answer 1

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.

Answer 2

This first of all makes me wonder about the database design that requires this...

Just in case it might help you, a different approach:

select case mytable.mycolumn
  when 1 then column1
  when 2 then column2
  when ...
  else ...
  as my_semi_dynamic_column
from ...

Answer 3

I would suggest looking at information_schema. The following code is untested but should theoretically work. Obviously replace your table name with an appropriate table name or link to information_schema.tables and use the table_type in your where clause

select concat('column', column_name) from information_schema.columns where table_name ='your table name'

Answer 4

You cannot meta-program SQL like that (unless stored procedures really are that powerful - I wouldn't know).

You should poll your column-logic out a level and generate your SQL in whatever programming language you use to invoke this query with. This, of course, is under the assumption that the query is invoked programatically in the first place.

If this is for ad hoc queries, then I would simply modify the SQL to fit every time I wanted to run the query.

But I will humbly submit that SQL simply is not designed for this kind of meta-programming.