By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table HYCategory(
HalfYearly char(6),Period char(7),Category varchar(6), AmountSGD decimal(8,6));
insert into HYCategory values
('1H2022','2022-01','BLANK',40.502761 ),
('1H2022','2022-01','PAINT',0),
('1H2022','2022-01','PART',31.765716 ),
('1H2022','2022-02','BLANK',40.253307 ),
('1H2022','2022-02','PAINT',15.458344 ),
('1H2022','2022-02','PART',22.193282 ),
('1H2022','2022-03','BLANK',49.759203 ),
('1H2022','2022-03','PAINT',4.804571 ),
('1H2022','2022-03','PART',33.198568 ),
('1H2022','2022-04','BLANK',0),
('1H2022','2022-04','PAINT',0),
('1H2022','2022-04','PART',0),
('1H2022','2022-05','BLANK',0),
('1H2022','2022-05','PAINT',0),
('1H2022','2022-05','PART',0),
('1H2022','2022-06','BLANK',0),
('1H2022','2022-06','PAINT',0),
('1H2022','2022-06','PART',0),
('2H2022','2022-07','BLANK',80.502761 ),
('2H2022','2022-07','PAINT',0),
('2H2022','2022-07','PART',51.765716 ),
('2H2022','2022-08','BLANK',70.253307 ),
('2H2022','2022-08','PAINT',25.458344 ),
('2H2022','2022-08','PART',32.193282 ),
('2H2022','2022-09','BLANK',39.759203 ),
('2H2022','2022-09','PAINT',14.804571 ),
('2H2022','2022-09','PART',53.198568 );
27 rows affected
select HalfYearly,Period,Category,AmountSGD
from HYCategory
union all
select HalfYearly,'', Category, sum(AmountSGD) SGD
from HYCategory
group by HalfYearly, Category
with rollup;
| HalfYearly | Period | Category | AmountSGD |
|---|---|---|---|
| 1H2022 | 2022-01 | BLANK | 40.502761 |
| 1H2022 | 2022-01 | PAINT | 0.000000 |
| 1H2022 | 2022-01 | PART | 31.765716 |
| 1H2022 | 2022-02 | BLANK | 40.253307 |
| 1H2022 | 2022-02 | PAINT | 15.458344 |
| 1H2022 | 2022-02 | PART | 22.193282 |
| 1H2022 | 2022-03 | BLANK | 49.759203 |
| 1H2022 | 2022-03 | PAINT | 4.804571 |
| 1H2022 | 2022-03 | PART | 33.198568 |
| 1H2022 | 2022-04 | BLANK | 0.000000 |
| 1H2022 | 2022-04 | PAINT | 0.000000 |
| 1H2022 | 2022-04 | PART | 0.000000 |
| 1H2022 | 2022-05 | BLANK | 0.000000 |
| 1H2022 | 2022-05 | PAINT | 0.000000 |
| 1H2022 | 2022-05 | PART | 0.000000 |
| 1H2022 | 2022-06 | BLANK | 0.000000 |
| 1H2022 | 2022-06 | PAINT | 0.000000 |
| 1H2022 | 2022-06 | PART | 0.000000 |
| 2H2022 | 2022-07 | BLANK | 80.502761 |
| 2H2022 | 2022-07 | PAINT | 0.000000 |
| 2H2022 | 2022-07 | PART | 51.765716 |
| 2H2022 | 2022-08 | BLANK | 70.253307 |
| 2H2022 | 2022-08 | PAINT | 25.458344 |
| 2H2022 | 2022-08 | PART | 32.193282 |
| 2H2022 | 2022-09 | BLANK | 39.759203 |
| 2H2022 | 2022-09 | PAINT | 14.804571 |
| 2H2022 | 2022-09 | PART | 53.198568 |
| 1H2022 | BLANK | 130.515271 | |
| 1H2022 | PAINT | 20.262915 | |
| 1H2022 | PART | 87.157566 | |
| 1H2022 | null | 237.935752 | |
| 2H2022 | BLANK | 190.515271 | |
| 2H2022 | PAINT | 40.262915 | |
| 2H2022 | PART | 137.157566 | |
| 2H2022 | null | 367.935752 | |
| null | null | 605.871504 |
SELECT * FROM
(
select [Period],AmountSGD
from [HYCategory]
) t
PIVOT(
max(AmountSGD)
FOR [Period] IN (
['2022-01'], ['2022-02'], ['2022-03'],
['2022-04'], ['2022-05'], ['2022-06'],
['2022-07'], ['2022-08'], ['2022-09'],
['1H2022'], ['2H2022'])
) AS pivot_table;
| '2022-01' | '2022-02' | '2022-03' | '2022-04' | '2022-05' | '2022-06' | '2022-07' | '2022-08' | '2022-09' | '1H2022' | '2H2022' |
|---|---|---|---|---|---|---|---|---|---|---|
| null | null | null | null | null | null | null | null | null | null | null |
create view pivoine as
select * FROM
(
select Period,Category,AmountSGD
from HYCategory
union all
select HalfYearly, Category, sum(AmountSGD) AmountSGD
from HYCategory
group by HalfYearly, Category
) t
PIVOT(
sum(AmountSGD)
FOR Period IN (
[1H2022],
[2022-01], [2022-02], [2022-03],
[2022-04], [2022-05], [2022-06],
[2H2022],
[2022-07], [2022-08], [2022-09]
)
) AS pivot_table
select coalesce(category,'Total') Category,
sum([1H2022]) as '1H2022',
sum([2022-01]) as '2022-01',
sum([2022-02]) as '2022-02',
sum([2022-03]) as '2022-03',
sum([2022-04]) as '2022-04',
sum([2022-05]) as '2022-05',
sum([2022-06]) as '2022-06',
sum([2H2022]) as '2H2022',
sum([2022-07]) as '2022-07',
sum([2022-08]) as '2022-08',
sum([2022-09]) as '2022-09'
from pivoine
group by category
with rollup
| Category | 1H2022 | 2022-01 | 2022-02 | 2022-03 | 2022-04 | 2022-05 | 2022-06 | 2H2022 | 2022-07 | 2022-08 | 2022-09 |
|---|---|---|---|---|---|---|---|---|---|---|---|
| BLANK | 130.515271 | 40.502761 | 40.253307 | 49.759203 | 0.000000 | 0.000000 | 0.000000 | 190.515271 | 80.502761 | 70.253307 | 39.759203 |
| PAINT | 20.262915 | 0.000000 | 15.458344 | 4.804571 | 0.000000 | 0.000000 | 0.000000 | 40.262915 | 0.000000 | 25.458344 | 14.804571 |
| PART | 87.157566 | 31.765716 | 22.193282 | 33.198568 | 0.000000 | 0.000000 | 0.000000 | 137.157566 | 51.765716 | 32.193282 | 53.198568 |
| Total | 237.935752 | 72.268477 | 77.904933 | 87.762342 | 0.000000 | 0.000000 | 0.000000 | 367.935752 | 132.268477 | 127.904933 | 107.762342 |
with pivotted as (
select * FROM
(
select Period,Category,AmountSGD
from HYCategory
union all
select HalfYearly, Category, sum(AmountSGD) AmountSGD
from HYCategory
group by HalfYearly, Category
) t
PIVOT(
sum(AmountSGD)
FOR Period IN (
[1H2022],
[2022-01], [2022-02], [2022-03],
[2022-04], [2022-05], [2022-06],
[2H2022],
[2022-07], [2022-08], [2022-09]
)
) AS pivot_table )
select coalesce(category,'Total') Category,
sum([1H2022]) as '1H2022',
sum([2022-01]) as '2022-01',
sum([2022-02]) as '2022-02',
sum([2022-03]) as '2022-03',
sum([2022-04]) as '2022-04',
sum([2022-05]) as '2022-05',
sum([2022-06]) as '2022-06',
sum([2H2022]) as '2H2022',
sum([2022-07]) as '2022-07',
sum([2022-08]) as '2022-08',
sum([2022-09]) as '2022-09'
from pivotted
group by category
with rollup
| Category | 1H2022 | 2022-01 | 2022-02 | 2022-03 | 2022-04 | 2022-05 | 2022-06 | 2H2022 | 2022-07 | 2022-08 | 2022-09 |
|---|---|---|---|---|---|---|---|---|---|---|---|
| BLANK | 130.515271 | 40.502761 | 40.253307 | 49.759203 | 0.000000 | 0.000000 | 0.000000 | 190.515271 | 80.502761 | 70.253307 | 39.759203 |
| PAINT | 20.262915 | 0.000000 | 15.458344 | 4.804571 | 0.000000 | 0.000000 | 0.000000 | 40.262915 | 0.000000 | 25.458344 | 14.804571 |
| PART | 87.157566 | 31.765716 | 22.193282 | 33.198568 | 0.000000 | 0.000000 | 0.000000 | 137.157566 | 51.765716 | 32.193282 | 53.198568 |
| Total | 237.935752 | 72.268477 | 77.904933 | 87.762342 | 0.000000 | 0.000000 | 0.000000 | 367.935752 | 132.268477 | 127.904933 | 107.762342 |