dynamic column name selection in MySql

Question

I am stuck in Mysql today with the dynamic column name need in mysql select statement. Let me explain:

sql> select name1 from namescollection. 
sql> select name2 from namescollection. 
sql> select name3 from namescollection. 

So namescollection table has three columns having name1, name2, name3 I would like to query this table in my stored procedure being 1,2,3 as dynamic and would be passed as a variable, but on the simple sql too when i query:

SELECT concat('name','1') FROM `namescollection` 

name1 ----- name1 rather fetching name1 field's value.

Can any suggest on this, the right function i need to use rather concat though I know its right to output name1 when I am calling concat but I want to use it as column name.

Answer 1

What you can do is use a prepared statement within your stored procedure which will allow you to execute a string query:

As a simple example:

DELIMITER //
CREATE PROCEDURE selname (IN col VARCHAR(20))
BEGIN
  SET @sql = CONCAT('SELECT ', col, ' FROM tbl');
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END//
DELIMITER ;

---

Test it out with this SQLFiddle Demo