How to pivot in bigQuery using PIVOT?

I am trying to pull rows as columns in bigquery. This is how my data looks like now:

This is how I want my data to look like:

PS: While I have shown only 3 values in column SUB_CLASS_DESC actual count is in 100s. Hence, I am looking to use Procedural language as per documentation here. I followed the example shared here in towardsdatascience.com and wrote below code, but unfortunately that doesn't work:

DECLARE DEPT_CLASS_SUB_CLASS STRING;
SET DEPT_CLASS_SUB_CLASS = (SELECT CONCAT('("', STRING_AGG(DISTINCT DEPT_CLASS_SUB_CLASS, '", "'), '")')
FROM `analytics-mkt-cleanroom.Workspace.HS_AF_SG_R12_800K_SAMPLE_SALES_11_TEST`
);

EXECUTE IMMEDIATE FORMAT("""
CREATE OR REPLACE TABLE `analytics-mkt-cleanroom.Workspace.HS_AF_SG_R12_800K_SAMPLE_SALES_PIVOTED_12_TEST` AS 
SELECT * FROM 
(SELECT HH_ID,DEPT_CLASS_SUB_CLASS,SALE_AMT 
FROM `analytics-mkt-cleanroom.Workspace.HS_AF_SG_R12_800K_SAMPLE_SALES_11_TEST`
)
PIVOT
(SUM(SALE_AMT)
,FOR DEPT_CLASS_SUB_CLASS IN %s
)""",DEPT_CLASS_SUB_CLASS);

Error I am getting:

Error message suggests to declare before the execute block, and I am doing exactly that, but I don't understand why the error still persists. I tried declaring variables DEPT_CLASS_SUB_CLASS in different ways but not successful yet. Could anyone please point out where I might be making the mistake.

Much appreciated!

Consider below approach

execute immediate (select '''
  select *
  from your_table
  pivot (any_value(sale_amt) for replace(sub_class_desc, ' ', '_') in (''' || list || '''))
  '''
  from (
    select string_agg(distinct "'" || replace(sub_class_desc, ' ', '_') || "'") list
    from your_table
  )
)     

if applied to dummy data as in your question - output is

How can I save these results into a new pivoted table? Specifically where can I put my CREATE OR REPLACE TABLE?

execute immediate (select '''
  create or replace table `your_project.your_dataset.pivot_table` as 
  select *
  from your_table
  pivot (any_value(sale_amt) for replace(sub_class_desc, ' ', '_') in (''' || list || '''))
  '''
  from (
    select string_agg(distinct "'" || replace(sub_class_desc, ' ', '_') || "'") list
    from your_table
  )
);