MySQL - How to read column value using variable name

Question

I have table: BILL:

+-----------------
+ ID | b31 | b32 |
+-----------------
   1  1.99   4.67
------------------
   2  0.46   2.54
------------------

I im using this MySQL query to add 'b' to match column name in above table (from my previus query i get number 31) so this is what i use:

SELECT CONCAT('b', '31') FROM `bill` WHERE id=1;

I get output => b31 what is column name and i want to get that column value..so in this example i would like to get:

 +------------
 + ID | b31 |
 +------------
  1    1.99

I im beginner in MySQL and could not figure out why this is not working? Any help is welcome.

Thanks.

Is there a b33? This kind of problem is symptomatic of poor design — Strawberry, Jan 29 '20 at 12:19
There is b33, b34 until b199...if it helps i can use only numbers for column name...but need to get value from column name... — user2631534, Jan 29 '20 at 12:21
It's not working because mysql does not do variable substitution - you could use prepared statements and dynamic sql https://dev.mysql.com/doc/refman/8.0/en/sql-prepared-statements.html or you could unpivot your data before counting (search for mysql unpivot) — P.Salmon, Jan 29 '20 at 12:30
See: https://stackoverflow.com/questions/13282718/dynamic-conversion-of-string-into-column-name-mysql — Adeel Siddiqui, Jan 29 '20 at 12:30
I see preparement statements but i don't understand how it works..could you please give me example using above example in my question? — user2631534, Jan 29 '20 at 13:20

score 2 · Answer 1 · answered Jan 29 '20 at 12:21

2

A normalized design:

ID* ref* amount
 1  b31  1.99    
 1  b32  4.67
 2  b31  0.46   
 2  b32  2.54

( * = component of PRIMARY KEY)

answered Jan 29 '20 at 12:21

Strawberry

1 Answers1