The SQL Server PIVOT operator helps you construct a solution starting with rows and rotating it around columns. It is a useful tool for dealing with relational databases in particular. Inside this article we will examine the numerous options readily available in SQL Server for pivoting from rows to columns, with PIVOT, with the UNPIVOT operator, and also with CROSS APPLY. We’ll also look at advanced scenarios like Dynamic PIVOT options and handle NULL values in PIVOT result sets.

Transposing Data

The PIVOT operator was introduced in SQL Server 2005 to assist users easily transform rows to columns, and the UNPIVOT operator to allow simple transformation of columns to rows. The latest release of Microsoft SQL Server 2014 boosts the PIVOT operator for simple and fast conversion in one column into another. The results of the example exhibited this processing as In a phenomenal way.

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 sales data for three salespersons for a couple of items.

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’ve now transformed the items from rows to columns, for each salesperson. Each and every product we wish to transform needs to have its own logic or script to transform its structure from rows to columns. This is an issue with the PIVOT operator which was addressed out.

NULL Values in the PIVOT Operator

By default the PIVOT operator will give you NULL for values when there are no matching items. You can use the AS keyword to change that behavior. If there is no value in a field, the result of the query will be 0 or blank.

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;

It’s possible to use the PIVOT operator to transpose rows into columns.

Dynamic Pivot Operation

We could easily transpose the following 5 items into columns by specifying the pivot operation in an appropriate clause. The below query returns the count of different value in table. If there are more than two different values for any column then it is showing error in above query. 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 ('+@listCol+')) AS pvt
				  ORDER BY PersonName'
	exec sp_executesql @query;
END

There are a couple of ways to use the PIVOT operator in dynamic queries. You can use the same syntax as we have seen before, or you can use the DYNAMIC syntax with a variable. Let’s see both ways to use PIVOT operator. In addition to that, we are dynamically fetching the column names into a variable named $listCol. It’s the fastest way to perform an online data transformation using the PIVOT operator and without modifying the SELECT clause.

Executing this query will produce the results as seen below.

NULL Values in Dynamic PIVOT Queries

Null values can be handled by using NULL values as an alternative to blank or 0 values for the Dynamic PIVOT query. You can also handle this NULL value scenario by using a dynamic pivot query where you can include null values for both the value and blank values.

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 ('+@listCol+')) AS pvt
				  ORDER BY PersonName'
	exec sp_executesql @query;
END

Using the above example, you can execute a dynamic pivot query that will display NULL values as zero.

Let’s try adding 2 more items to the sales table and run the above query to see if it can dynamically display those values.

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

Result:

Conclusion

Today we’ve learned to transform the data structure using pivot commands in SQL Server. We’ve learned how to convert a table to JSON format, to write queries that transform one to the other, and to use PIVOT to convert one to the other.

You may have noticed that in the PIVOT query, we had to add a HAVING clause to get rid of rows that were not grouped. Now that we understand how to deal with NULLs, you can remove this constraint. We also discussed how to dynamically execute the PIVOT operation for unknown column list values.

Make sure to download and install the SQL Complete add-in for SSMS before you start SQL coding. This will help you speed up SQL typing.