I have a table structured like this:
| Customer_number | Category | Object | Period | Quarter | Price | Rate | Actuals_cost | Forecast |
|---|---|---|---|---|---|---|---|---|
| 1 | Home | bulb | DEC23 | FY23-Q4 | 3435 | 4563 | 355 | 2423525 |
| 2 | Toys | soft toys | OCT23 | FY23-Q4 | 3535 | 54646 | 6436 | 45346 |
| 3 | CDs | Music | FEB23 | FY23-Q1 | 4657 | 334 | 5757 | 574744 |
I want to transform this table into the following format, where the Period values become columns:
| Customer_number | Category | Object | Period | Quarter | DEC23_Price | DEC23_Rate | DEC23_Actual_cost | OCT23_Price | OCT23_Rate | OCT23_Actual_cost | FEB23_Price | FEB23_Rate | FEB23_Actual_cost | Total_Price | Total_Rate | Total_Actual_cost | Forecast |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Home | bulb | DEC23 | FY23-Q4 | 3435 | 4563 | 355 | 3435 | 4563 | 355 | 2423525 | ||||||
| 2 | Toys | soft toys | OCT23 | FY23-Q4 | 3535 | 54646 | 6436 | 3535 | 54646 | 6436 | 45346 | ||||||
| 3 | CDs | Music | FEB23 | FY23-Q1 | 4657 | 334 | 5757 | 4657 | 334 | 5757 | 574744 |
Logic Needed:
I want to pivot the Period column into separate columns for each unique period, filling in the corresponding values for Price, Rate, and Actuals_cost. Additionally, I need to calculate total columns for Total_Price, Total_Rate, and Total_Actual_cost, which will be calculated row-wise.
Could anyone guide me on how to achieve this transformation using Snowflake SQL?
2