dates in result column name-CodePudding

Good day dear ones. Please help me to query the database.

schema

Schema (MySQL v8.0)

create table TEST_TABLE (
    DT date,
    OBJECT_ID NUMERIC,
    PARAM_VALUE NUMERIC
);

insert TEST_TABLE (DT, OBJECT_ID, PARAM_VALUE) values ('2022-01-01',1, 21);
insert TEST_TABLE (DT, OBJECT_ID, PARAM_VALUE) values ('2022-01-02',1, 34);
insert TEST_TABLE (DT, OBJECT_ID, PARAM_VALUE) values ('2022-01-03',1, 78);
insert TEST_TABLE (DT, OBJECT_ID, PARAM_VALUE) values ('2022-01-04',1, 11);
insert TEST_TABLE (DT, OBJECT_ID, PARAM_VALUE) values ('2022-01-05',1, 26);
insert TEST_TABLE (DT, OBJECT_ID, PARAM_VALUE) values ('2022-01-06',1, 11);
insert TEST_TABLE (DT, OBJECT_ID, PARAM_VALUE) values ('2022-01-07',1, 12);
insert TEST_TABLE (DT, OBJECT_ID, PARAM_VALUE) values ('2022-01-08',1, 74);

insert TEST_TABLE (DT, OBJECT_ID, PARAM_VALUE) values ('2022-01-01',2, 18);
insert TEST_TABLE (DT, OBJECT_ID, PARAM_VALUE) values ('2022-01-02',2, 96);
insert TEST_TABLE (DT, OBJECT_ID, PARAM_VALUE) values ('2022-01-03',2, 33);
insert TEST_TABLE (DT, OBJECT_ID, PARAM_VALUE) values ('2022-01-04',2, 77);
insert TEST_TABLE (DT, OBJECT_ID, PARAM_VALUE) values ('2022-01-05',2, 55);
insert TEST_TABLE (DT, OBJECT_ID, PARAM_VALUE) values ('2022-01-06',2, 28);
insert TEST_TABLE (DT, OBJECT_ID, PARAM_VALUE) values ('2022-01-07',2, 54);
insert TEST_TABLE (DT, OBJECT_ID, PARAM_VALUE) values ('2022-01-08',2, 16);

Query #1

select * from TEST_TABLE;

DT	OBJECT_ID	PARAM_VALUE
2022-01-01	1	21
2022-01-02	1	34
2022-01-03	1	78
2022-01-04	1	11
2022-01-05	1	26
2022-01-06	1	11
2022-01-07	1	12
2022-01-08	1	74
2022-01-01	2	18
2022-01-02	2	96
2022-01-03	2	33
2022-01-04	2	77
2022-01-05	2	55
2022-01-06	2	28
2022-01-07	2	54
2022-01-08	2	16

Query #2

set @TARGET_DATE = '2022-01-08';

select
OBJECT_ID,
group_concat(if(DT = DATE_ADD(@TARGET_DATE, interval -1 day) , PARAM_VALUE, null)) as `dt-1`,
group_concat(if(DT = DATE_ADD(@TARGET_DATE, interval -2 day) , PARAM_VALUE, null)) as `dt-2`
from TEST_TABLE group by OBJECT_ID;

OBJECT_ID	dt-1	dt-2
1	12	11
2	54	28

I would like to receive result with dates in column names

OBJECT_ID	2022-01-07	2022-01-06
1	12	11
2	54	28

Sorry about my English

CodePudding user response：

As Akina said, prepared statement:

SET @TARGET_DATE = '2022-01-08';

SELECT GROUP_CONCAT(DISTINCT CONCAT(
        'GROUP_CONCAT(IF(DT="',DT,'", PARAM_VALUE, NULL)) AS "',DT,'"')
        ORDER BY DT DESC
        SEPARATOR ', \r\n' )
        INTO @columns
FROM TEST_TABLE
WHERE DT >= DATE_ADD(@TARGET_DATE, interval -2 day) 
AND DT <= DATE_ADD(@TARGET_DATE, interval -1 day);

SELECT CONCAT('SELECT OBJECT_ID,',@columns,' 
                 FROM TEST_TABLE 
                 GROUP BY OBJECT_ID;') INTO @sql;

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

The final query for this will be different from your original attempt but it's based on it:

SELECT OBJECT_ID,
       GROUP_CONCAT(IF(DT="2022-01-07", PARAM_VALUE, NULL)) AS "2022-01-07", 
       GROUP_CONCAT(IF(DT="2022-01-06", PARAM_VALUE, NULL)) AS "2022-01-06" 
FROM TEST_TABLE 
GROUP BY OBJECT_ID;

I purposely separate the creation of @columns and the final @sql query in hope that it's more easy to understand but it is possible to use a single query for the prepared statement once you've got the hang of it.

Demo fiddle