Get column value for last 3 years in single select statement (MYSQL)

user_8395063 0
Viewed 27

let's say I have the table below:

id user_id level date_created
1 1 2 2019-07-01T18:53:41.000Z
2 1 3 2020-07-01T18:53:41.000Z
3 1 4 2021-07-01T18:53:41.000Z
4 2 2 2020-07-01T18:53:41.000Z

Let's say I want to get a user's level for each of the last 3 years, based on date created, in a single row.

Something like:

SELECT user_id, (some query here) AS "level_2019"
      , (some query here) AS "level_2020", (some query here) AS "level_2021"  
FROM table

Is this something that's possible for a single select statement? I'd also like the level for that year to be null if they don't have one for that year.

Thank you!

mysql sql database
1 Answers

Because of the dynamic aliases you need dynamuc sql for such a query

CREATE TABLE table1 (
  `id` INTEGER,
  `user_id` INTEGER,
  `level` INTEGER,
  `date_created` varchaR(24)
);

INSERT INTO table1
  (`id`, `user_id`, `level`, `date_created`)
VALUES
  ('1', '1', '2', '2019-07-01T18:53:41.000Z'),
  ('2', '1', '3', '2020-07-01T18:53:41.000Z'),
  ('3', '1', '4', '2021-07-01T18:53:41.000Z'),
  ('4', '2', '2', '2020-07-01T18:53:41.000Z');

SET @column_alias1 := CONCAT('level_',YEAR(NOW() - INTERVAL 3 Year));
SET @column_alias2 := CONCAT('level_',YEAR(NOW() - INTERVAL 2 Year));
SET @column_alias3 := CONCAT('level_',YEAR(NOW() - INTERVAL 1 Year));
SET @query := CONCAT('SELECT `user_id`,
MIN( IF (YEAR(CAST(`date_created` AS DATETIME)) = YEAR( NOW() - INTERVAL 3 YEAR), `level`,NULL)) as `', @column_alias1, '` 
,MIN( IF (YEAR(CAST(`date_created` AS DATETIME)) = YEAR( NOW() - INTERVAL 2 YEAR), `level`,NULL)) as `', @column_alias2, '`
,MIN( IF (YEAR(CAST(`date_created` AS DATETIME)) = YEAR( NOW() - INTERVAL 1 YEAR), `level`,NULL)) as `', @column_alias3, '`
FROM table1
GROUP BY `user_id`');
PREPARE dynamic_statement FROM @query;
EXECUTE dynamic_statement



user_id | level_2019 | level_2020 | level_2021
------: | ---------: | ---------: | ---------:
      1 |          2 |          3 |          4
      2 |       null |          2 |       null

db<>fiddle here

user_51935360
Related