CODEWITHSUNDEEP
mysql
Obularaju Dommaraju
Obularaju Dommaraju

Reputation: 11

Mysql variable value as column in select clause

I am assigning a variable through below select query output. 

 select group_concat(COLUMN_NAME) 
  from 
    ( select  distinct COLUMN_NAME 
       FROM information_schema.`COLUMNS` C  
     WHERE table_name = 'ADM_METERQUEUE' 
        AND COLUMN_NAME LIKE '%maxrate%' 
      order 
         by 1 desc 
      limit 5) as ids 
  INTO @COLUMNS

Assigned variable has output like below.

select @COLUMNS

o/p :: maxrate23,maxrate22,maxrate21,maxrate20,maxrate19

When I am using in select clause i am getting like below.

select @COLUMNS from ADM_METERQUEUE where meterqueueid=38

o/p :: maxrate23,maxrate22,maxrate21,maxrate20,maxrate19

I want to achieve like below, please let me know how to do it.

Desired Output::

select maxrate23,maxrate22,maxrate21,maxrate20,maxrate19 from ADM_METERQUEUE where meterqueueid=38;

+-----------+-----------+-----------+-----------+-----------+
| maxrate23 | maxrate22 | maxrate21 | maxrate20 | maxrate19 |
+-----------+-----------+-----------+-----------+-----------+
|       2   |   7       |  4        |    4      |  1        |
+-----------+-----------+-----------+-----------+-----------+

select group_concat(COLUMN_NAME)    from      ( select  distinct COLUMN_NAME         FROM information_schema.`COLUMNS` C        WHERE table_name = 'ADM_METERQUEUE'          AND COLUMN_NAME LIKE '%maxrate%'        order           by 1 desc        limit 5) as ids    INTO @COLUMNS;

PREPARE stmt FROM 'select ? from ADM_METERQUEUE';

 EXECUTE stmt USING @COLUMNS;

o/p :: maxrate23,maxrate22,maxrate21,maxrate20,maxrate19

Still i am getting same column names as output Server version: 5.6.37-82.2-56-log Percona XtraDB Cluster (GPL), Release rel82.2, Revision 114f2f2, WSREP version 26.21, wsrep_26.21

Upvotes: 0

Views: 53

Answers (1)

Paul Campbell
Paul Campbell

Reputation: 1976

You are very nearly there, but this section from the docs should complete the picture

Statement names are not case-sensitive. preparable_stmt is either a string literal or a user variable that contains the text of the SQL statement. The text must represent a single statement, not multiple statements. Within the statement, ? characters can be used as parameter markers to indicate where data values are to be bound to the query later when you execute it. The ? characters should not be enclosed within quotation marks, even if you intend to bind them to string values. Parameter markers can be used only where data values should appear, not for SQL keywords, identifiers, and so forth.

In other words, you can use a data value such as meterqueueid as a bound variable but not the column identifiers collected in @COLUMNS.

Assuming that meterqueueid is also being sourced from another variable, then something like this should work

SET @mqid = 38;
SET @sql = CONCAT('SELECT ', @COLUMNS, ' FROM ADM_METERQUEUE where meterqueueid=?');

PREPARE stmt FROM @sql;
EXECUTE stmt USING @mqid;
DEALLOCATE PREPARE stmt;

Upvotes: 1

Related Questions