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.

I think... you can use another SELECT inside of the CONCAT, if not you'll have to create a stored procedure. Haven't got a MysQL here to test, so comment instead of answer. — TimothyP, Jun 12 '09 at 09:41

score 27 · Accepted Answer · edited Jul 07 '15 at 08:48

27

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
;

edited Jul 07 '15 at 08:48

Dominique

1,080
14
29

answered Jun 12 '09 at 10:57

Chris Vest

8,642
3
35
43

I think that's the case and I have to accept that I can't do what I want in one query, as nice as it would have been. – markus Jun 12 '09 at 10:59
1

@tharkun I have reversed my answer. Note that according to the documentation, this feature requires at least version 5.0 of MySQL. – Chris Vest Feb 15 '11 at 10:13

Arjan · Answer 2 · 2016-02-12T13:11:53.537

15

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

edited Feb 12 '16 at 13:11

answered Jun 12 '09 at 09:47

Arjan

22,808
11
61
71

thanks for the idea. it won't work because it has to be completely generic. – markus Jun 12 '09 at 10:14
Reading your question again, I see you're using numbers. So, I changed the "when 'a' then columna" into "when 1 then column1". Is the number of the 'columnxx' columns in your table really that high...? – Arjan Jun 12 '09 at 10:21
yes, it's quite high and it changes. so I need something dynamic and generic. – markus Jun 12 '09 at 10:52
Very useful tip, you just forget to add the "end" before "as" instruction. Thanks! – Lionel Feb 12 '16 at 11:56
Thanks & fixed, @Lionel. – Arjan Feb 12 '16 at 13:03
1

This definitely works and answers the question of the OP... unlike the accepted answer... thanks!! – Dominique Jul 19 '17 at 11:29
What an elegant construction, I use it first time. Thanks a lot! – Eugene Kaurov Nov 23 '22 at 11:24

score 3 · Answer 3 · answered Jun 12 '09 at 09:41

3

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.

answered Jun 12 '09 at 09:41

Darryl Hein

142,451
95
218
261

could you elaborate on scary? the data to concatenate is an auto_incremented ID (primary). – markus Jun 12 '09 at 10:13
Please revise your question. If the number you're using is an auto_incremented ID then how many columns do you expect to have in your table? I guess I don't understand the question, as I hope this table does not have columns like "column1", "column2, ..., "column90000", ... – Arjan Jun 12 '09 at 10:28
sort of but not really. the columns are results to single questions, one user per row. so it's a result set of questionnaire results. every question has an id and that id is included in the column name. – markus Jun 12 '09 at 10:57
1

If it's not too late to change the database design then I'd do so... For now: what about just using "select *" and parse the results elsewhere? – Arjan Jun 12 '09 at 11:04
maybe I'll try to discuss this one day on SO but I think a full normalization of my design would be suboptimal compared to what I have now. my db is 90% normalized and normalizing that specific part would mean much more complex queries and fetching hundreds of rows instead of one in so many places that I prefer to have one smaller problem which I will solve now just by writing two queries instead of one. – markus Jun 12 '09 at 11:19
1

Don't your dynamic table definitions (and hence the unlimited number of tables) make future upgrades (or worse: rollbacks of upgrades...) of your application kind of troublesome as well? – Arjan Jun 14 '09 at 08:47

score 0 · Answer 4 · answered Jun 12 '09 at 09:54

0

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'

answered Jun 12 '09 at 09:54

Steve

21
3

why should it work for selection in the information_schema if it doesn't work otherwise? – markus Jun 12 '09 at 10:11
This fetches the column names, not the contents of the column (which name is dynamic). – Arjan Jun 12 '09 at 10:17
Ehhh, but you want to select the columns VALUES, right? Not the NAMES? I think the above will not help you select from dynamic column names. Selecting the column names (without selecting) does not need any rocket science -- it's actually already done in the example you posted in your question. – Arjan Jun 12 '09 at 10:24
SELECT col.`COLUMN_NAME` FROM `information_schema`.`COLUMNS` AS col WHERE col.`TABLE_NAME` LIKE 'mytable' AND col.`COLUMN_NAME` = CONCAT( 'column', 35 ) – markus Jun 12 '09 at 10:54
but the resulting column name is not accepted as a column name. so same problem as before. – markus Jun 12 '09 at 10:55

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

4 Answers4

Linked