SQL Server PIVOT operator

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.

Transposing Data

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.

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;

Syntax Explanation

  • 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:

UNPIVOT Operator

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;

Where

  • 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: