By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE t
(account_no int, tran_date datetime, value int)
;
INSERT INTO t
(account_no, tran_date, value)
VALUES
(123, '2021-11-22 00:00:00', 500),
(123, '2021-11-01 00:00:00', 500),
(123, '2020-11-20 00:00:00', 1500),
(123, '2022-06-03 00:00:00', 5000),
(123, '2021-06-04 00:00:00', 2000),
(456, '2020-11-03 00:00:00', 525),
(456, '2021-11-04 00:00:00', 125)
;
7 rows affected
select *
from t
| account_no | tran_date | value |
|---|---|---|
| 123 | 2021-11-22 00:00:00.000 | 500 |
| 123 | 2021-11-01 00:00:00.000 | 500 |
| 123 | 2020-11-20 00:00:00.000 | 1500 |
| 123 | 2022-06-03 00:00:00.000 | 5000 |
| 123 | 2021-06-04 00:00:00.000 | 2000 |
| 456 | 2020-11-03 00:00:00.000 | 525 |
| 456 | 2021-11-04 00:00:00.000 | 125 |
select *
from
(
select account_no
,value
,concat(datename(month, tran_date), '_', dense_rank() over(partition by month(tran_date) order by year(tran_date) desc)) as month_rnk
from t
) t
pivot (sum(value) for month_rnk in(June_1, June_2, November_1, November_2)) p
| account_no | June_1 | June_2 | November_1 | November_2 |
|---|---|---|---|---|
| 123 | 5000 | 2000 | 1000 | 1500 |
| 456 | null | null | 125 | 525 |