Question 20107 in Mysql Question posted in
The official MySQL documentation can be found here.

Dynamically SELECT columns based on column name and date reference – Mysql

Our Table currently looks like this:

Record ID | Name | … | Value_2301 | Value_2302 | Value_2303| …

Where 2301, 2302 and 2303 are dates in YY-MM format. Due to the nature of our system, as the years go by, the table will continue to grow wider as more columns are added (eg. Value_2401, Value_2402). This isn’t the best of practices but I don’t think we will be changing this practice any time soon.

We would like a query which retrieves only Values that are dated 1 year back and 1 year in advance (so since we are in year 2023, the columns to retrieve are Value_2201 to Value_2412), without hardcoding any years.

My initial idea is to use the following syntax:

select CONCAT('Value_', CONCAT(right(year(current_date())-1,2), '01')); -- returns Value_2201
select CONCAT('Value_', CONCAT(right(year(current_date())-1,2), '02')); -- returns Value_2202
...
select CONCAT('Value_', CONCAT(right(year(current_date()),2), '01')); -- returns Value_2301
...
select CONCAT('Value_', CONCAT(right(year(current_date()) + 1, 2), '12')) -- returns Value_2412

to create a dynamic list of column names that we would like to retrieve. Ideally, I would like to somehow fit this list into the query so that the query will always retrieve Value columns that are within 1 year back and 1 year in advance, but I am at lost of how to do so, or if it even is feasible.

Would greatly appreciate any help and feedback from you guys.

Thanks in advance!

Tags:

2

Answers


  1. 0

    There is a possibility of creating & executing dynamic SQLs in MySQL.

    SET @qStr = (
SELECT CONCAT('SELECT Value_', CONCAT(RIGHT(YEAR(CURRENT_DATE())-1,2), '01'), ' FROM tbl')
);

PREPARE stmt FROM @qStr;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

    Here, the variable @qStr will be a string having the following value in it as on today:
    SELECT Value_2201 FROM tbl

    Here, Value_2201 will be dynamically added based on the date.
    You can create any complex query like this.

    And then,

    PREPARE stmt FROM @qStr;
EXECUTE stmt;

    These two will prepare an executable statement from that string and execute it.

    Login or Signup to reply.
  2. 0

    This expands on Ishan’s answer, using the information_schema to provide the list of column names:

    SELECT
    CONCAT(
        'SELECT id, name, ',
        GROUP_CONCAT(COLUMN_NAME ORDER BY ORDINAL_POSITION),
        ' FROM ' ,
        TABLE_NAME
    ) INTO @qStr
FROM information_schema.COLUMNS
WHERE TABLE_NAME = 'my_table'
AND TABLE_SCHEMA = 'test'
AND COLUMN_NAME BETWEEN CONCAT('Value_', DATE_FORMAT(CURRENT_DATE - INTERVAL 1 YEAR, '%y01'))
                    AND CONCAT('Value_', DATE_FORMAT(CURRENT_DATE + INTERVAL 1 YEAR, '%y12'))
GROUP BY TABLE_NAME;

PREPARE stmt FROM @qStr;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search