SQL Pivot Table with sum of half yearly

Question

I would like to ask about the SQL Pivot Table with dynamic columns and total.

I've two tables Table 1 let call table #HYCategory

and the second table call table #DataSummary, assume that the data 1H2022 and 2H2022 is same.

And at the end, I want the output like

How could I do that pivot?

Data:

CREATE TABLE #HYCategory
(
Category NVARCHAR(100)
,HalfYearly NVARCHAR(100)
,AmountSGD NUMERIC(22,4)
)
INSERT INTO #HYCategory VALUES
('BLANK','1H2022','130.515271'),
('PAINT','1H2022','20.262915'),
('PART','1H2022','87.157566'),
('BLANK','2H2022','130.515271'),
('PAINT','2H2022','20.262915'),
('PART','2H2022','87.157566')

CREATE TABLE #DataSummary
(
HalfYearly NVARCHAR(100)
,[Period] NVARCHAR(100)
,Category NVARCHAR(100)
,AmountSGD NUMERIC(22,4)
)
INSERT INTO #DataSummary 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','40.502761'),
('2H2022','2022-07','PAINT','0'),
('2H2022','2022-07','PART','31.765716'),
('2H2022','2022-08','BLANK','40.253307'),
('2H2022','2022-08','PAINT','15.458344'),
('2H2022','2022-08','PART','22.193282'),
('2H2022','2022-09','BLANK','49.759203'),
('2H2022','2022-09','PAINT','4.804571'),
('2H2022','2022-09','PART','33.198568'),
('2H2022','2022-10','BLANK','0'),
('2H2022','2022-10','PAINT','0'),
('2H2022','2022-10','PART','0'),
('2H2022','2022-11','BLANK','0'),
('2H2022','2022-11','PAINT','0'),
('2H2022','2022-11','PART','0'),
('2H2022','2022-12','BLANK','0'),
('2H2022','2022-12','PAINT','0'),
('2H2022','2022-12','PART','0')

DROP TABLE #HYCategory,#DataSummary

Answer 1

Here is the query with pivot using with rollup to give the totals.
The table with the total is not necessary, if you want it create a view.
See dbFiddle link below.
NB I've modified the figure because there was a lot of repetition and I wanted it to be clear that we were not duplicating results.

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
GO

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

db<>fiddle here