let's say I have the table below:

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!

CodePudding user response：

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