Dynamic conversion of string into column name. MySQL

Question

I have two tables: items and orders

items
--------------
id (int) | type_1 (int) | type_2  (int)|

orders
--------------
id (int) | transaction_type enum ('type_1', 'type_2')

Basically, I want to do the following:

select (select transaction_type from orders where id=1) from items;

So, the problem is that string returned by select transaction_type from orders where id=1, cannot be converted into column name.

What is the error you get when running that query? I seem to get the correct response, but I may be misunderstanding the question :) — RocketDonkey, Nov 08 '12 at 05:12
@RocketDonkey it returns me number of strings 'type_1' (by number of rows in items) — Torro Buden, Nov 08 '12 at 05:59
Gotcha, my bad - posted something that may be of use (sorry if I misunderstood again). — RocketDonkey, Nov 08 '12 at 06:18

score 12 · Answer 1 · edited May 23 '17 at 12:16

You may want to see the answer to this question, which I believe is what you're trying to accomplish. In short, the answer suggests using prepared statements in order to simulate an eval()-esque functionality. In your case, this may work (you can see the SQLFiddle here:

SELECT transaction_type FROM orders WHERE id=1 into @colname;
SET @table = 'items';
SET @query = CONCAT('SELECT ',@colname,' FROM ', @table);

PREPARE stmt FROM @query;
EXECUTE stmt;

I won't claim to be any sort of expert on the underlying mechanics at work, but per the comments it seems to achieve the goal. Again, this was adopted from another answer, so if it works makes sure to +1 that one :)

Mahmoud Gamal · Answer 2 · 2012-11-08T05:22:29.063

the problem is that string returned by select transaction_type from orders where id=1, cannot be converted into column name

You have to PIVOT the values of the column transaction_type of the orders table like so:

    SELECT 
      id,
      MAX(CASE WHEN transaction_type = 'type_1' THEN 1 END) Type1,
      MAX(CASE WHEN transaction_tyep = 'type_2' THEN 2 END) type2
    FROM orders
    GROUP BY id

Then you can JOIN the two tables like so:

SELECT i.id - what do you want to select
FROM items i
INNER JOIN
(
    SELECT 
      id,
      MAX(CASE WHEN transaction_type = 'type_1' THEN 1 END) Type1,
      MAX(CASE WHEN transaction_tyep = 'type_2' THEN 2 END) type2
    FROM orders
    GROUP BY id
) o ON i.type_1 = o.type1 AND i.type_2 = o.type2 -- you can add more conditions

Dynamic conversion of string into column name. MySQL

2 Answers2

Linked