Welcome to Excel Jet Consult blog post. In this blog, I am going to walk you through how to create a cross-tab report in Fabric Warehouse using the Pivot Operator. Let’s get started.
Video
Pivot Operator
The PIVOT operator in SQL Server and Fabric Data Warehouse allows you to transform rows into columns and perform aggregations on your data. One of of the benefit of using the operator is that it improves the readability and clarity of the data by creating more focused views that display the relevant information in a matrix format. It also simplifies the reporting and analysis of the data by reducing the query complexity and the need for multiple joins or subqueries. In addition, it streamlines the ETL processes by enabling data transformation and summarization in a single step.
Cross Tab Report in Fabric
We want to focus on how to use the operator to perform cross-tab report in Fabric Data Warehouse. We’re going to use a table called data in the Data Warehouse and by writing a simple SELECT * FROM data, we returned all the records in the Data table as seen in the screenshot below
We want to write a SQL query that will display the Units by Year, Products (Speaker, Laptop and Camera) and Payment Type that equals to PayPal. The three products will be displayed as columns by the Year, as rows.
To do the above, execute the query:
FROM data
WHERE PaymentType =’PayPal’) AS SelectedColumns
(
SUM(Units) FOR Product IN ([Speaker], [Laptop], [Camera])
) AS PVT
The screenshot below, the result is presented in a pivoted format, where each product category has its own column, and the values represent the total units sold in each year.
