title: SQL Pivot Table with sum of half yearly
body: <p>I would like to ask about the SQL Pivot Table with dynamic columns and total.</p>
  <p>I've two tables
  Table 1 let call table <code>#HYCategory</code></p>
  <p><a href="https://i.stack.imgur.com/yV9rT.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/yV9rT.png" alt="enter image description here" /></a></p>
  <p>and the second table call table <code>#DataSummary</code>, assume that the data 1H2022 and 2H2022 is same.</p>
  <p><a href="https://i.stack.imgur.com/1hBQq.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/1hBQq.png" alt="enter image description here" /></a></p>
  <p>And at the end, I want the output like</p>
  <p><a href="https://i.stack.imgur.com/TV1An.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/TV1An.png" alt="enter image description here" /></a></p>
  <p>How could I do that pivot?</p>
  <p>Data:</p>
  <pre><code>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
  </code></pre>