CODEWITHSUNDEEP
mysql
o17t H1H' S'k
o17t H1H' S'k

Reputation: 2725

how to write a mysql view which uses a column name from data in a table

I have a "parameters" table containing in its data a column name ("col1"), which is one of the columns in my "mydata" table.

parameters
==========
which_column    
-----------
"col1"


mydata
======
col1    col2    col3
--------------------
6       7       42
1       2       3
5       9       11

I need the write a mysql view which selects from "mydata" depending on the column specified in mydata

edit:

i want to do something like:

create view myview as
    select mydata.(select which_column from parameters) from mydata

i have 24 different column names so i do not want to manually test for each case.

Upvotes: 1

Views: 123

Answers (2)

vadchen
vadchen

Reputation: 1462

In case table parameters contains only one row with column name this should work:

SELECT which_column FROM parameters INTO @param;
SET @qry = CONCAT('SELECT ', @param, ' FROM mydata');
PREPARE stmt1 FROM @qry;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;

Upvotes: 1

Eugen Rieck
Eugen Rieck

Reputation: 65254

SELECT 
  IF(@parameter='col1',col1,IF(@parameter='col2',col2,IF(@parameter='col3',col3,null))) AS mycol
FROM
  tablename;

This is q&d, but it works.

Upvotes: 0

Related Questions