SQL Server’s PIVOT operator is commonly used to transpose values in the rows format to the table in the columns format. Transposing rows to columns is part of the standard functionality of SQL data, and this article explains some of the most-frequently-accessed options. We’ll go into advanced scenarios, such as handling.
With the release of SQL Server 2005 came the PIVOT operator, a useful tool that allowed users to transpose rows to columns and transpose columns to rows. PIVOT operators preceded the feature by Microsoft, but if there were to write multiple T-SQL queries, it was useful for operators. Tableau allows you to create a pivot table in SQL with any number of dimensions or measures in the same page.
CREATE TABLE Sales (PersonName varchar(100), Item varchar(100), Quantity int, Amount Money); INSERT INTO Sales Values ('RRJ', 'Apples', 5, 10); INSERT INTO Sales Values ('John', 'Apples', 4, 8); INSERT INTO Sales Values ('Sam', 'Apples', 6, 12); INSERT INTO Sales Values ('RRJ', 'Oranges', 10, 30); INSERT INTO Sales Values ('John', 'Oranges', 15, 45); INSERT INTO Sales Values ('RRJ', 'Peaches', 25, 50); INSERT INTO Sales Values ('Sam', 'Peaches', 35, 70); INSERT INTO Sales Values ('John', 'Cherry', 10, 10); INSERT INTO Sales Values ('Sam', 'Cherry', 25, 25); INSERT INTO Sales Values ('RRJ', 'Banana', 50, 25);
Selecting from the sales table, we can see some sales data across 3 salespersons for a couple of items (in our case it is fruits).
SELECT * FROM Sales;
Here’s an example to practice your basic aggregate operations:
SELECT PersonName, SUM(Amount) Sales FROM Sales GROUP BY PersonName
This is easy to do. We’ll have 5 items to find out the sales per item across 3 salespersons:
SELECT PersonName , SUM(CASE WHEN Item = 'Apples' THEN Amount ELSE 0 END) Apples , SUM(CASE WHEN Item = 'Oranges' THEN Amount ELSE 0 END) Oranges , SUM(CASE WHEN Item = 'Peaches' THEN Amount ELSE 0 END) Peaches , SUM(CASE WHEN Item = 'Cherry' THEN Amount ELSE 0 END) Cherry , SUM(CASE WHEN Item = 'Banana' THEN Amount ELSE 0 END) Banana FROM Sales GROUP BY PersonName
We have transformed the items from rows to columns by each salesperson. We can make each item in a spreadsheet transform into a column based on a formula. We’d need to write a script or logic to do this. This is the major drawback with this approach which was addressed out in the PIVOT operator.
The UNPIVOT operator takes one or more columns of data and transposes. The syntax of the PIVOT operator would be:
SELECT all_non_pivot_columns, all_pivot_columns FROM (SELECT_QUERY_containing_the_data) AS alias_name PIVOT ( Aggregate_operation(aggregate_column) FOR pivotable_column IN ( pivotable_column_values) ) AS alias_name_pivot_table ORDER_BY_clause;
- All of our nonpivot columns are non-pivoted like our PersonName example in our previous example.
- The relative order of several products, such as apples, oranges, and the like, is accounted for by the last column we transposed or pivoted out.
- SQL SELECT statement containing the data refer to the query that is used to output the data from the Sales table with the appropriate filters as described.
- Alias_name allude to the expression source query.
- Our example is the SUM operation on aggregate operations, and the aggregate operation in economics is the MAX operation.
- The column indicated in the Aggregate_column refers to the Sum column on which we performed the aggregate operation.
- The column to which we are attempting to transpose or PIVOT is the Pivotable Column.
- Item column values refer to the values available in the Column values column like apples, oranges with quoted values eg, “[Apples]”, “[Oranges]”, etc.,
- The alias name for the Pivot operation is called the Pivot operation.
- ORDER_BY_clause is optional.
Replace the PIVOT operator syntax with the actual values. Our final script would be.
SELECT PersonName, Apples, Oranges, Peaches, Cherry, Banana FROM (SELECT PersonName, Item, Amount FROM Sales) AS src PIVOT ( SUM(Amount) FOR Item IN ( [Apples],[Oranges],[Peaches],[Cherry],[Banana]) ) AS pvt ORDER BY PersonName;
Executing as shown below:
A UNPIVOT query with a PIVOT operator as the source can be used as a source for the UNPIVOT operator and transform. You have to use the syntax of the UNPIVOT operator to use the query.
SELECT all_columns_list FROM (SELECT non_pivot_columns, all_pivot_columns FROM pvt) p UNPIVOT (aggregate_value_column FOR unpivottable_column IN (Column_names_list) )AS unpvt;
- This function is used to unpivot the list data into columns in the Data View.
- The non-pivoted columns are the columns in your table that do not pivot.
- pivot_unpivot_all() takes the data frame, unpivots all columns, and transposes all columns into rows.
- Aggregate_value_coloumn is the name of the column with aggregated values.
- Unpivotable_columns refers to the columns which should be shown in a single column for all these values together.
Now we can use the UNPIVOT operator to turn this data into columns, as shown in the example below:
SELECT PersonName, Item, Amount FROM ( -- PIVOT Query Start SELECT PersonName, Apples, Oranges, Peaches, Cherry, Banana FROM (SELECT PersonName, Item, Amount FROM Sales) AS src PIVOT ( SUM(Amount) FOR Item IN ( [Apples],[Oranges],[Peaches],[Cherry],[Banana]) ) AS pvt -- PIVOT Query Ends ) p UNPIVOT (Amount FOR Item IN ([Apples],[Oranges],[Peaches],[Cherry],[Banana]) )AS unpvt;
Executing the query with the same parameters as above returns the following result set as shown below: