MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table pivot_cols (
  pivot_by_cols_id int auto_increment primary key,
  my_date date not null, 
  cat_1_id int not null, 
  cat_2_id int not null, 
  my_value int not null
);
insert into pivot_cols (my_date, cat_1_id, cat_2_id, my_value) values 
('2024-01-01', 1, 1, 1),
('2024-01-01', 2, 1, 2),
('2024-01-02', 1, 2, 3),
('2024-01-02', 2, 2, 4),
('2024-01-03', 1, 1, 5),
('2024-01-03', 2, 1, 6);

Records: 6  Duplicates: 0  Warnings: 0


select *, case when cat_2_id = 1 then my_value else (
  select my_value
  from pivot_cols as x
  where cat_1_id = pivot_cols.cat_1_id
  and cat_2_id = 1
  and my_date < pivot_cols.my_date
  order by my_date desc
  limit 1
) end as id_1_value, case when cat_2_id = 2 then my_value else (
  select my_value
  from pivot_cols as x
  where cat_1_id = pivot_cols.cat_1_id
  and cat_2_id = 2
  and my_date < pivot_cols.my_date
  order by my_date desc
  limit 1
) end as id_2_value
from pivot_cols
order by my_date, cat_1_id


pivot_by_cols_id
my_date
cat_1_id
cat_2_id
my_value
id_1_value
id_2_value


1
2024-01-01
1
1
1
1
null

2
2024-01-01
2
1
2
2
null

3
2024-01-02
1
2
3
1
3

4
2024-01-02
2
2
4
2
4

5
2024-01-03
1
1
5
5
3

6
2024-01-03
2
1
6
6
4


with cte as (
  select
    *,
    max(case when cat_2_id = 1 then my_date end) over prev_rows as date_1,
    max(case when cat_2_id = 2 then my_date end) over prev_rows as date_2
  from pivot_cols
  window prev_rows as (
    partition by cat_1_id
    order by my_date rows between unbounded preceding and 1 preceding
  )
)
select
  cte.*,
  case when cte.cat_2_id = 1 then cte.my_value else j1.my_value end as id_1_value,
  case when cte.cat_2_id = 2 then cte.my_value else j2.my_value end as id_2_value
from cte
left join pivot_cols as j1 on cte.cat_1_id = j1.cat_1_id and cte.date_1 = j1.my_date 
left join pivot_cols as j2 on cte.cat_1_id = j2.cat_1_id and cte.date_2 = j2.my_date
order by cte.my_date, cte.cat_1_id


pivot_by_cols_id
my_date
cat_1_id
cat_2_id
my_value
date_1
date_2
id_1_value
id_2_value


1
2024-01-01
1
1
1
null
null
1
null

2
2024-01-01
2
1
2
null
null
2
null

3
2024-01-02
1
2
3
2024-01-01
null
1
3

4
2024-01-02
2
2
4
2024-01-01
null
2
4

5
2024-01-03
1
1
5
2024-01-01
2024-01-02
5
3

6
2024-01-03
2
1
6
2024-01-01
2024-01-02
6
4

pivot_by_cols_id	my_date	cat_1_id	cat_2_id	my_value	id_1_value	id_2_value
1	2024-01-01	1	1	1	1	null
2	2024-01-01	2	1	2	2	null
3	2024-01-02	1	2	3	1	3
4	2024-01-02	2	2	4	2	4
5	2024-01-03	1	1	5	5	3
6	2024-01-03	2	1	6	6	4