SQL Server PIVOT Operator

In SQL Server, PIVOT is a powerful operator to transpose table values in the rows format to table values in column format. In this article, we will see the various options available in SQL Server to transpose rows to columns before the PIVOT operator, with PIVOT, and with the UNPIVOT operator. We’ll also explore advanced scenarios like Dynamic PIVOT options and handle NULL values in PIVOT result sets.

Transposing Data without the PIVOT Operator

Introduced in SQL Server 2005 as a new feature, the PIVOT operator was handy to transpose rows to columns easily with huge sets of data, and the UNPIVOT operator to transpose columns to rows. Before the PIVOT operator was introduced, we had to write several detailed T-SQL statements to achieve transposing. We can create a test table with data matching our requirements as shown below.

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);

Performing a SELECT on the test table, we can see some sales data across 3 salespersons for a couple of items (in our case it is fruits).

SELECT * 
FROM Sales;

Let’s try to do some basic Aggregate operations as shown below.

SELECT PersonName, SUM(Amount) Sales
FROM Sales
GROUP BY PersonName

Since we have 5 items, to find out the sales per item across 3 salespersons, we can execute the below query.

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

Now, we have transformed the items from rows to columns across each salesperson. As we can see clearly for every item we need to transform, we would need to write logic or scripts to transform from rows to columns. This is the major drawback with this approach which was addressed out in the PIVOT operator.

PIVOT Operator

The UNPIVOT operator was introduced to transpose columns to rows. 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

1. All_non_pivot_columns refer to the columns which aren’t pivoted like PersonName in our previous example
2. All_pivot_columns refer to the Items like apples, oranges, etc., which we have transposed or Pivoted out.
3. SELECT_QUERY_containing_the_data refer to the query which is used to fetch the data from the Sales table with necessary filter conditions as required.
4. Alias_name refer to the alias name for the source query.
5. Aggregate_operation refers to using MAX or SUM or any other aggregate operation on the Amount column which in our example is the SUM operation.
6. Aggregate_column refers to the Amount column on which we have performed the Aggregate operation.
7. Pivotable_column refers to the column on which we are trying to transpose or PIVOT which in our case is the Item column.
8. Pivotable_column_values refer to the values available in the Item column like apples, oranges with quoted values like [Apples], [Oranges], etc.,
9. Alias_name_pivot_table refers to the alias name for the Pivot operation.
10. ORDER_BY_clause is optional

Now that we have understood the PIVOT operator syntax, let’s 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 the above query will yield the result as shown below:

UNPIVOT Operator

We can use the PIVOT operator query as a source for the UNPIVOT operator and transpose the records from columns to rows. The syntax of the UNPIVOT operator would be.

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

1. All_columns_list refers to the list of all columns from the Unpivot list.
2. Non_pivot_columns refer to the normal columns not involved in transposing.
3. All_pivot_columns refer to the list of columns that needs to be unpivoted or transposed from columns to rows
4. Aggregate_value_column refers to the column with aggregated values that need to be transposed to rows
5. Unpivottable_column refers to the column which should be shown in a single column for all these values together.

Using the earlier PIVOT operator query as a source, we can create our UNPIVOT query as shown 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 above query yields the below result set matching our original Sales table as shown below.

Handling NULL Values in the PIVOT Operator

PIVOT operator by default will yield NULL values if there are no matching items available for a particular PersonName. To display 0 or blank instead of NULL, we might need to use an ISNULL() function in the final SELECT statement as shown below:

SELECT PersonName
	, ISNULL(Apples,0) Apples
	, ISNULL(Oranges,0) Oranges
	, ISNULL(Peaches,0) Peaches
	, ISNULL(Cherry,0) Cherry
	, ISNULL(Banana,0) 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;

Now, we have successfully transposed rows to columns using the PIVOT operator along with handling NULL values in the result set.

Dynamic Pivot Operation

In the above example, we have seen the list of 5 items involved and we have specified these 5 item values in the PIVOT operation to transpose these 5 items as columns. If these 5 values are not constant, the above PIVOT script might not be working fine. To accommodate the changing values, we would need to perform and verify the PIVOT operation using dynamic SQL statements.

DECLARE @listCol nvarchar(2000);
DECLARE @query nvarchar(4000);

IF (SELECT COUNT(*) FROM Sales) > 0
BEGIN
	-- Dynamically Create Column Names
	SET @listCol = STUFF(( SELECT DISTINCT
				   '],[' + ltrim(cast(Item as varchar(100)))
				   FROM Sales
				   ORDER BY '],[' + ltrim(cast(Item as varchar(100)))
				   FOR XML PATH('') ), 1, 2, '') + ']'

	-- Dynamically Create PIVOT operation
	SET @query = 'SELECT *
				  FROM
				  (SELECT PersonName, Item, Amount
				   FROM Sales) src
				  PIVOT (SUM(Amount) FOR Item
				  IN ('[email protected]+')) AS pvt
				  ORDER BY PersonName'

	exec sp_executesql @query;

END

In the above script, we use the same PIVOT operator syntax but with Dynamic SQL statements and assign all the PIVOT script to a variable named @query and later execute the PIVOT script from the @query by using sp_executesql system stored procedure.

In addition to that, we are dynamically fetching the column names into a variable named @listCol. Any changes to the item values will get reflected automatically and don’t require changing the column names in the PIVOT operation or the SELECT clause as well.

Executing the above query will yield the result as shown below.

Handling NULL Values in Dynamic PIVOT Queries

To handle the NULL values with 0 or blanks in the Dynamic PIVOT query, we need to declare additional variables to dynamically create the column names with the ISNULL function included along with another variable to hold the non-pivot as shown below.

DECLARE @listCol nvarchar(2000);
DECLARE @listCol_isnull nvarchar(2000);
DECLARE @nonpivot_column nvarchar(200);
DECLARE @query nvarchar(4000);

IF (SELECT COUNT(*) FROM Sales) > 0
BEGIN
	-- Dynamically Create Column Names
	SET @listCol = STUFF(( SELECT DISTINCT
				   '],[' + ltrim(cast(Item as varchar(100)))
				   FROM Sales
				   ORDER BY '],[' + ltrim(cast(Item as varchar(100)))
				   FOR XML PATH('') ), 1, 2, '') + ']'

	-- Dynamically Create Column Names list with ISNULL function
	SET @listCol_isnull = STUFF(( SELECT DISTINCT
',ISNULL(' + ltrim(cast(Item as varchar(100))) + ',0) ' + + ltrim(cast(Item as varchar(100)))
				FROM Sales
				ORDER BY ',ISNULL(' + ltrim(cast(Item as varchar(100))) + ',0) ' + + ltrim(cast(Item as varchar(100)))
				FOR XML PATH('') ), 1, 1, '') 

	-- Variable to hold Non pivotable columns
	SET @nonpivot_column = 'PersonName,'

	-- Dynamically Create PIVOT operation
	SET @query = 'SELECT ' + @nonpivot_column + @listCol_isnull + 
				 ' FROM
				  (SELECT PersonName, Item, Amount
				   FROM Sales) src
				  PIVOT (SUM(Amount) FOR Item
				  IN ('[email protected]+')) AS pvt
				  ORDER BY PersonName'

	exec sp_executesql @query;

END

Executing the above query, we can get the PIVOT done dynamically with all NULL values displayed as 0 instead of NULL values.

Let’s try adding 2 more items to the Sales table and execute the above query to see whether it can dynamically display those values.

INSERT INTO Sales Values ('Sam', 'Mango', 4, 20);
INSERT INTO Sales Values ('RRJ', 'Pineapple', 10, 30);

Executing our dynamic PIVOT script yields the below result set.

New Item values are getting added automatically to our PIVOT Result set.

Conclusion

Today, we have explored various approaches to transpose rows to columns in SQL Server. We have learned how to transpose the records from rows to columns before the PIVOT operator was introduced and how PIVOT and UNPIVOT operators helped transpose the rows to columns and columns to rows efficiently. In addition to that, we have seen how to handle the NULL values that come in the PIVOT result. We also discussed how to dynamically execute the PIVOT operation for unknown column list values. To make SQL typing in SSMS faster, there is a helpful add-in SQL Complete. We will meet again with another interesting article soon.