SQL Server 2022

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

create table bad_schema (
  some_id integer not null,
  month1 integer,
  month2 integer,
  month3 integer,
  month4 integer,
  month5 integer,
  month6 integer,
  month7 integer,
  month8 integer,
  month9 integer,
  month10 integer,
  month11 integer,
  month12 integer,
  year integer not null,
  primary key(some_id, year)
);
insert into bad_schema values
  (1, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 2024),
  (1, 1, 2, 3, 4, 5, 6, 7, 8, null, 0, 1, 2, 2023),
  (2, 11, 12, 13, -4, -5, 16, 27, 38, null, 0, 1, 2, 2023);
3 rows affected

select * from generate_series(1, 12) num


value


1

2

3

4

5

6

7

8

9

10

11

12


create view good_schema as (
select 
  some_id,
  cast(concat(year, '-', months.value, '-', '01') as date) as this_month,
  case months.value
  when 1 then month1
  when 2 then month2
  when 3 then month3
  when 4 then month4
  when 5 then month5
  when 6 then month6
  when 7 then month7
  when 8 then month8
  when 9 then month9
  when 10 then month10
  when 11 then month11
  when 12 then month12
  else 'what'
  end as data
from bad_schema
cross join (
  select * from generate_series(1, 12)
) as months
);
select * from good_schema;


some_id
this_month
data


1
2023-01-01
1

1
2023-02-01
2

1
2023-03-01
3

1
2023-04-01
4

1
2023-05-01
5

1
2023-06-01
6

1
2023-07-01
7

1
2023-08-01
8

1
2023-09-01
null

1
2023-10-01
0

1
2023-11-01
1

1
2023-12-01
2

1
2024-01-01
11

1
2024-02-01
12

1
2024-03-01
13

1
2024-04-01
14

1
2024-05-01
15

1
2024-06-01
16

1
2024-07-01
17

1
2024-08-01
18

1
2024-09-01
19

1
2024-10-01
20

1
2024-11-01
21

1
2024-12-01
22

2
2023-01-01
11

2
2023-02-01
12

2
2023-03-01
13

2
2023-04-01
-4

2
2023-05-01
-5

2
2023-06-01
16

2
2023-07-01
27

2
2023-08-01
38

2
2023-09-01
null

2
2023-10-01
0

2
2023-11-01
1

2
2023-12-01
2


select sum(data) 
from good_schema
where this_month between '2023-01-01' and '2023-05-01'
group by some_id


(No column name)


15

27

some_id	this_month	data
1	2023-01-01	1
1	2023-02-01	2
1	2023-03-01	3
1	2023-04-01	4
1	2023-05-01	5
1	2023-06-01	6
1	2023-07-01	7
1	2023-08-01	8
1	2023-09-01	null
1	2023-10-01	0
1	2023-11-01	1
1	2023-12-01	2
1	2024-01-01	11
1	2024-02-01	12
1	2024-03-01	13
1	2024-04-01	14
1	2024-05-01	15
1	2024-06-01	16
1	2024-07-01	17
1	2024-08-01	18
1	2024-09-01	19
1	2024-10-01	20
1	2024-11-01	21
1	2024-12-01	22
2	2023-01-01	11
2	2023-02-01	12
2	2023-03-01	13
2	2023-04-01	-4
2	2023-05-01	-5
2	2023-06-01	16
2	2023-07-01	27
2	2023-08-01	38
2	2023-09-01	null
2	2023-10-01	0
2	2023-11-01	1
2	2023-12-01	2