Pivot and Unpivot in Alteryx and SQL

MediumSQL · PostgreSQL

Problem

How would you pivot and unpivot the same table in Alteryx and in SQL?

Use the sales_matrix table below. Write SQL that returns the pivoted result, then write SQL that converts that pivoted result back to the original row format.

Output

For the pivoted result, return one row per region with separate columns for each product_name.
For the unpivoted result, return one row per region and product_name pair with the corresponding sales_amount.
Keep the output deterministic by sorting by region, then product_name where applicable.

Output details

Return only the final three columns in this order: region, product_name, sales_amount.
Use the fixed product values Laptop, Mouse, and Keyboard, regardless of which products appear in the data.
Represent absent product amounts as numeric 0.
Sort both region and product_name in ascending order.

Schema

sales_matrix

Column	Type	Description
sales_idPK	INT	Unique row identifier
region	VARCHAR(50)	Sales region
product_name	VARCHAR(50)	Product name
sales_amount	INT	Sales amount for the region and product

Tablessales_matrix

How would you pivot and unpivot the same table in Alteryx and in SQL?

Use the sales_matrix table below. Write SQL that returns the pivoted result, then write SQL that converts that pivoted result back to the original row format.

Output

For the pivoted result, return one row per region with separate columns for each product_name.
For the unpivoted result, return one row per region and product_name pair with the corresponding sales_amount.
Keep the output deterministic by sorting by region, then product_name where applicable.

Output details

Return only the final three columns in this order: region, product_name, sales_amount.
Use the fixed product values Laptop, Mouse, and Keyboard, regardless of which products appear in the data.
Represent absent product amounts as numeric 0.
Sort both region and product_name in ascending order.