I’m trying to create a pivot table in Snowflake where the columns are dynamic based on the data. For example, see the following table:

CREATE OR REPLACE TABLE sales_data ( Category VARCHAR, Subcategory VARCHAR, Amount NUMBER );

INSERT INTO sales_data (Category, Subcategory, Amount) VALUES ('A', 'X', 100), ('A', 'Y', 150), ('B', 'X', 200), ('B', 'Z', 250);

I want to pivot this data so that each unique subcategory becomes a column, and the amounts are filled in accordingly.
The issue I’m facing is that the subcategories can change, and I want the pivot to be dynamic, adapting to the data in the table.
How can I achieve this in Snowflake? Any help or example would be greatly appreciated!

I tried to solve it using joins but this is not a flexible and dynamical solution.