Up next in 10
Cross Tab Report in Fabic Data Warehouse using PIVOT Operator
13K views
Jan 29, 2024
The Pivot operator allows transforming rows into columns and performing aggregation on data. It improves the readability and clarity of the query. This video shows how to create cross-tab report in Fabric Warehouse using the Pivot operator.
View Video Transcript
0:00
Hello there. In this video, I'm going to show you how to use the pivot operator in
0:10
Fabric Data Warehouse. Basically, the pivot operator rotates a table-valued expression by turning the unit values from a single column in an expression into multiple columns in
0:22
the output. So I'm going to show you how to create a cross-tabular report that shows
0:27
the units by year, products, and for payment type that equal to PayPal. Enough of talking
0:35
let's get started. Now, based on what we can see on the screen, we have this simple
0:39
SELECT star from data table. So the first thing I want to do is to select the three
0:46
columns, the year, products, and the unit, and then we'll apply the WHERE clause. So
0:52
we want to choose all the rows where the payment type is equal to PayPal. So let's jump into
0:57
the code. So SELECT. I'm going to select the year, products, and the units. And we'll
1:08
SELECT from data and let's apply the WHERE clause. So WHERE payment type is equal to
1:20
PayPal. Okay, so let's run this code and see what this is delivering for now. And there
1:30
we go. So we can see we have the year, the product, and the unit. Now, this is going
1:34
to be like an inner query. So what I'm going to do is I'm going to wrap this inside opening
1:41
parentheses and close these parentheses. Okay, so I'm going to just move this down a little
1:45
bit. Now, I'm going to come to the top and use the SELECT star from. Now, that's going
1:51
to select from what this is delivering. And of course, I can come outside and apply alias
1:56
so as SELECTED columns. Now, this way becomes a little bit tricky. I'm going to use the
2:03
pivot operator in here. So for the pivot operator, I'm just going to open and close brackets
2:09
And inside the open and close brackets, now I'm going to use the SUM aggregate function
2:14
So SUM, and of course, we want to call the units column, close that and we want to use
2:19
the FOR. Now, we actually want to display the product, this specific three products
2:24
the speaker, laptop, and camera in the columns while the year is in the row. So for this
2:31
we're going to just put in the name of the column. So product in, and then we can specify
2:38
inside a square bracket, the list of the specific product. So the first one is speaker
2:45
and then put in a comma. So in the game, in the square brackets, we're going to select
2:49
the laptop. Okay, laptop. And of course, finally, we want the camera. So camera. Okay, there
2:59
we go. And then we can just alias this as PVT for pivot table. Now, let's go through
3:06
the code again. Let me just move this a little bit down. Now, first, we selected the three
3:11
columns, the year, the product, and the unit from the data table. And of course, we apply
3:17
the WHERE clause to filter for the rows where the payment type is equal to PayPal. And of
3:23
course, we alias these as selected or derived table. Next, we applied the pivot operator
3:31
And of course, we specify the unit as the column to aggregate using the sum function
3:37
And then we use the for operator from the product in the speaker, laptop and camera
3:44
And then we alias this as PT for pivot table. So let's go ahead and run this code. And let's
3:49
see the results. Amazing. So we have the results, we can see the year, the speaker, the laptop
3:55
and the camera. So we can see the units. And it's going to be lovely if we can order
4:01
the year in descending order. So we can apply the order by clause. So order by year, and
4:08
then we can apply descending order. So select and click Run. And there we go. So we can
4:15
see we have the 2020, 2019, 28, 2017, and so on. And of course, we can see that the
4:23
best selling products in terms of units in 2020 is laptop with a total unit of 45,296
4:31
And of course, let's talk about the 2015. In 2015, the best selling product is camera
4:40
in terms of units with total units of 45,399 units sold. We can apply further criteria
4:49
So let's say we want to see the payment type that equal to PayPal or credit slash debit
4:55
card. So I'm going to get rid of this equal to and use the in operator. So in and then
5:02
open a bracket. So I'm going to come here, close the bracket and put in a comma. So inside
5:08
single quotes, we want to see credit for slash debit cards. Okay, I think this should be
5:15
correct. So let's run query and let's see the results. And there we go. So we can see
5:23
now that we applied to criteria in the payment type that equals to PayPal and the debit and
5:30
credit cards. So we can see that we have a new result. And of course, we can still see
5:35
that in 2020, the best selling product in terms of units sold is laptop with the value
5:42
of 60,197 units sold. And of course, in 2015, the best selling product is camera with total
5:52
units sold of 61 to 40. So this is basically how we can use the pivot operator in fabric
6:01
data warehouse. I trust you enjoyed this video. If you do like, comment and share with your
6:06
friends. Thank you and bye for now. Cheers
#Business Services
#Enterprise Technology
#Retail Equipment & Technology
#Software
#Warehousing