CODEWITHSUNDEEP
phpmysqlsql
user16084902
user16084902

Reputation:

Dynamic column name within select

I am a begginer with MySQL.

I have one simple table, where the column names are the names of the months. This table is called 'Tarif'

I also have one function procedure which returns a varchar with the name of the month and, receive as input a datetime. This function is called 'getMonth', and would work something like:

getMonth('2021-01-04')
and this returns: 'January'

The table with be something like this:

 id | January | February | March | ...
  1      P1        P2       P3
  2      P3        P1       P2

Doing a simple query like:

select * from Tarif where id = 1

I obtain

 id | January | February | March | ...
  1      P1        P2       P3

My objective is to change the query to something like:

select getMonth('2021-01-04') from Tarif where id = 1

and obtain that 'P1'.

It seems this this would be something easy to do, but this actually returns only what the function is returning: 'January'

Thank you all!!

Upvotes: 0

Views: 193

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

This requires either dynamic SQL or explicit logic:

select (case getMonth( . . .)
            when 'January' then january
            when 'February' then february
            . . .
        end) as tarif
from tarif
where id = 1;

Note: this is cumbersome because you have a poor data model. You should really be storing the values in separate rows, with three columns:

id      monthname     tarif

Then you could just use:

select *
from tarif_good
where id = 1 and monthname = . . .;

Also, writing your own function to extract the month is a bit silly when monthname() already does this -- and it respects the internationalization settings for a wide variety of languages.

Upvotes: 2

Related Questions