How to create a dynamic pivot table in Snowflake?

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.

Solution

You could achieve the result with this query

SELECT 
    Category,
    MAX(CASE WHEN Subcategory = 'X' THEN Amount END) AS X,
    MAX(CASE WHEN Subcategory = 'Y' THEN Amount END) AS Y,
    MAX(CASE WHEN Subcategory = 'Z' THEN Amount END) AS Z
FROM sales_data
GROUP BY Category;

but if you are trying to write it as a pivot (for future improvements) this is the code to use (I suggest looking at this example to understand how the code works)

-- Dynamic columns
SELECT *
FROM sales_data
PIVOT
 (MAX(Amount) 
 FOR Subcategory IN (SELECT DISTINCT Subcategory FROM sales_data ORDER BY Subcategory)
 ) AS PivotTable
ORDER BY 
 Category;