Can we use Pivot Function in Materialized Views in Snowflake DB

Question

Can we use Pivot Function in Materialized Views in Snowflake DB.Kindly respond.

Seen the documentation and it comes under " Nesting of subqueries within a materialized view." https://docs.snowflake.net/manuals/user-guide/views-materialized.html#label-limitations-on-materialized-views.

Answer 1

You can create a materialized view that includes a pivot, but the aggregate functions supported remain limited per the documentation to which you linked.

Answer 2

Yes , You can do. Below code worked.

CREATE OR REPLACE TABLE monthly_sales(empid INT, amount INT, month TEXT) AS SELECT * FROM VALUES (1, 10000, 'JAN'), (1, 400, 'JAN'), (2, 4500, 'JAN'), (2, 35000, 'JAN'), (1, 5000, 'FEB'), (1, 3000, 'FEB'), (2, 200, 'FEB'), (2, 90500, 'FEB'), (1, 6000, 'MAR'), (1, 5000, 'MAR'), (2, 2500, 'MAR'), (2, 9500, 'MAR'), (1, 8000, 'APR'), (1, 10000, 'APR'), (2, 800, 'APR'), (2, 4500, 'APR');

CREATE MATERIALIZED VIEW test1 AS (SELECT EMPID AS EMP_ID, "'JAN'" AS JANUARY, "'FEB'" AS FEBRUARY, "'MAR'" AS MARCH, "'APR'" AS APRIL FROM monthly_sales PIVOT(sum(amount) FOR MONTH IN ('JAN', 'FEB', 'MAR', 'APR')) AS p)