The actual values from the next column Totalsales, they stick with the pivot column headers. Now, the pivoting table would look like below. Pivoting is like rotating from vertical to horizontal. And un-pivoting is like rotating from horizontal to vertical. In the following example, the pivoted dataset is used to un-pivot the values. The key piece here is this was easy to do in getting transformed because we were able to create a pivot table as an intermediate step and then do the transformation using the unpivot operation.
Note: SQL pivot operation results in transposing the aggregated result into column but whereas SQL unpivot is not an exact reversal of transposing columns into the segregated values of rows.
The unpivot operator will not split the aggregated results. Before we jump into the solution, it is always recommended to work on creating a dataset for the PIVOT operation. Let us take a look into another example to see sales split data based on every month. Let us address the monthly split of sales data using dynamic pivoting.
You will notice that we have a column for each month. This is very valuable, you will also see that the rows have been grouped by the years.
So important to note here is that Pivot requires a name column, in our case Month, and a value column, in our case Total Sales. Something to look out for and understand is duplicate values. In our example, we have duplicate values for July Note here that the default aggregation of the Pivot function is SUM.
This is quite an important element to be aware off as this might easily return incorrect results if source data was not cleaned properly. If you expect the data to have duplicates then you need to just select your aggregation carefully. In the For clause, we need to pass the column name which contains values that are going to be column header.
In our case, it is the ProductName column. In the IN clause, we need to specify the Pivoted column names. In our example, it is a Laptop and Desktop. Let us execute the following code and see the output as expected. The following is the syntax of the Pivot Operator.
Let us understand this with an example. We are going to use the following ProductSales table to understand this concept. Let us visualize the above Product Sales data from a different perspective. For example, we want to tell the sales amount, per count, per agent as shown below.
Here, actually, we have to change the perspective of column-wise data into row-wise. Finally, we use a FOR statement to specify the pivot column and the unique values in it. The result looks like this:. For instance, if you have a table that looks like this:. The columns of the original table have been converted to the rows in the unpivoted table.
If you select the data from the Students table of the School2 database, you will see the following results:. You can see that there are no repeated rows. In other words, we can say that for each student there is only one record per course. For example, Sally has only one record for her score in the English course. To do this, execute the following script:. At the output, you will see the original Students table.
0コメント