Mysql variable value as column in select clause

Question

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

Google Dynamic SQL in MySQL. You should get relevant answers — Madhur Bhaiya
– Madhur Bhaiya, Commented Dec 2, 2018 at 12:25
Hi @MadhurBhaiya, Thanks for your response. 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;` 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 — Obularaju Dommaraju
– Obularaju Dommaraju, Commented Dec 2, 2018 at 17:25

Paul Campbell · Accepted Answer · 2018-12-05 22:08:54Z

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;

Collectives™ on Stack Overflow

Mysql variable value as column in select clause

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related