By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table sample
(
ID int,
Salary int,
Months int,
DOJMonth varchar(10)
)
insert into sample values
(1, 6000, 3, 'Mar'),
(2, 4000, 5, 'Jun');
2 rows affected
-- Method 1 : cross apply
with mthref as
(
select *
from (
values
('Jan', 1), ('Feb', 2), ('Mar', 3), ('Apr', 4), ('May', 5), ('Jun', 6),
('Jul', 7), ('Aug', 8), ('Sep', 9), ('Oct',10), ('Nov',11), ('Dec',12)
) M (DOJMonth, MthNo)
)
select s.ID, m.*
from sample s
inner join mthref r on s.DOJMonth = r.DOJMonth
cross apply
(
select [Jan] = case when r.MthNo <= 1 and r.MthNo + Months > 1 then Salary end,
[Feb] = case when r.MthNo <= 2 and r.MthNo + Months > 2 then Salary end,
[Mar] = case when r.MthNo <= 3 and r.MthNo + Months > 3 then Salary end,
[Apr] = case when r.MthNo <= 4 and r.MthNo + Months > 4 then Salary end,
[May] = case when r.MthNo <= 5 and r.MthNo + Months > 5 then Salary end,
[Jun] = case when r.MthNo <= 6 and r.MthNo + Months > 6 then Salary end,
[Jul] = case when r.MthNo <= 7 and r.MthNo + Months > 7 then Salary end,
[Aug] = case when r.MthNo <= 8 and r.MthNo + Months > 8 then Salary end,
[Sep] = case when r.MthNo <= 9 and r.MthNo + Months > 9 then Salary end,
[Oct] = case when r.MthNo <= 10 and r.MthNo + Months > 10 then Salary end,
[Nov] = case when r.MthNo <= 11 and r.MthNo + Months > 11 then Salary end,
[Dec] = case when r.MthNo <= 12 and r.MthNo + Months > 12 then Salary end
) m;
-- method 2 : pivot
with
mthref as
(
select *
from (
values
| ID | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | null | null | 6000 | 6000 | 6000 | null | null | null | null | null | null | null |
| 2 | null | null | null | null | null | 4000 | 4000 | 4000 | 4000 | 4000 | null | null |
| ID | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | null | null | 6000 | 6000 | 6000 | null | null | null | null | null | null | null |
| 2 | null | null | null | null | null | 4000 | 4000 | 4000 | 4000 | 4000 | null | null |