Snowflake Pivot Without Apostrophe

In Snowflake, if you Pivot rows to columns as such:

SELECT *
  FROM quarterly_sales
    PIVOT(SUM(amount) FOR quarter IN (ANY))
  ORDER BY empid;

You get the following results:

I would like to find a way to drop all of the apostrophes " ' " from the beginning and end of the column names. I know that I can specify the column names without the " ' ", but I need this pivot to be dynamic to the data (which is why the ANY is used).

Is there any way I can drop the " ' " from the column names, either in the pivot itself or as an update to the table?

I approached it using a custom procedure to update the column names since you mentioned this is an option.

In the documentation it is mentioned that alias can be used as p (employee, q1, q2, q3, q4) but I guess for that you need to know the quarters which does not fit the use case here to fetch the values dynamically.

First the table is created using the query you provided

CREATE OR REPLACE TEMPORARY TABLE pivoted_sales AS
SELECT *
FROM quarterly_sales
PIVOT(SUM(amount) FOR quarter IN (ANY))
ORDER BY empid;

Then this table pivoted_sales is looked up in information_schema and columns for the tables are iterated while checking if the column name starts with quote, if yes then quotes are removed using substring

I did try to use REPLACE in the Alter statement but could not make it work, so I used substring.

Sample procedure

CREATE OR REPLACE PROCEDURE update_quotes()
  RETURNS STRING
  LANGUAGE JAVASCRIPT
  EXECUTE AS CALLER
AS
$$
  var getColumnsStmt = snowflake.createStatement({sqlText: `SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'PIVOTED_SALES'`});
  var columnsResultSet = getColumnsStmt.execute();

  while (columnsResultSet.next()) {
    var columnName = columnsResultSet.getColumnValue(1);
    if (columnName.startsWith("'") && columnName.endsWith("'")) {
      var newColumnName = columnName.substring(1, columnName.length - 1); 
      var renameStmt = snowflake.createStatement({sqlText: `ALTER TABLE PIVOTED_SALES RENAME COLUMN "${columnName}" TO "${newColumnName}"`});
      renameStmt.execute();
    }
  }

  return 'Columns renamed successfully';
$$;

Call the procedure

CALL update_quotes() ;

Output

select * from pivoted_sales ;