Using PIVOT with SQL Server

02 April 2019 Admin DataBases

1. INTRODUCTION

PIVOT operator is mainly used to convert a result set from a SQL query into another one by rotating unique values in a specified field column, throughout the original result set, into new column fields in the final result set. This field is known as Spreading Column. These new columns will contain aggregated values according to:

  • Aggregated Column: a previously selected column to make calculations (sum, count, avg, etc.) over it.
  • Non-Pivoted Columns:the rest of fields in the original result set.

2. BACKGROUND

But, in short, what do we really want to achieve with PIVOT clause? Let's see it with a quick example.

Here is our ORIGINAL RESULT SET:

It is a result set obtained from a simple query returning a list of orders sold for each employee according to ship countries. In particular, the result set has been obtained from Northwind database over Orders table and any grouping operation has been performed.

And here is what we want and we can obtain for our FINAL RESULT SET just applying PIVOT operation:

And then, what has happened? Well, to begin with, distinct values in ShipCountry original field have been pivoting or rotating into new fields in the final result set. Secondly, every new cell value in final result set has been calculated from applying SUM function over Amount original field and grouping by EmployeeID and ShipCountry fields. In this example ShipCountry is the SPREADING COLUMN, Amount is the AGGREGATED COLUMN and EmployeeID is a NON-PIVOTED-COLUMN.

Here is the code executed in Northwind database to obtain previous result by means of PIVOT operator:

;with ORIGINAL_QUERY as (select e.EmployeeID, ShipCountry, Freight
from Orders o
join Employees e on
e.EmployeeID=o.EmployeeID
join Customers c on
o.CustomerId=c.CustomerId)
select EmployeeID, Austria,Finland,Italy,France,Germany,Brazil,Belgium,Switzerland
from ORIGINAL_QUERY
PIVOT (SUM(Freight) FOR ShipCountry IN (
  Austria,Finland,Italy,France,
  Germany,Brazil,Belgium,Switzerland)) as PIVOTED_QUERY
order by EmployeeID
 

Before going to examples let's see the basic syntax for PIVOT operator.

 
SELECT [Your list of non-pivoted columns],
       [First value in Spreading column],
       [Second value in Spreading column],
       ...
       [Last value in Spreading]
FROM (SELECT source query) AS [alias for the source query]
PIVOT
(
    Aggregation function([Aggregated column])
    FOR
        [spreading column or column that contains the values that will become column headers]
    IN ([First value in Spreading column], 
        [Second value in Spreading column],
        ... 
        [Last value in Spreading column])
) AS 
;

This article will show you how to generate values for Spreading Column, not only in a static way but dynamically as well. As a consequence it is important to stand out this little piece of code used to concatenate values from a specified column. So, using this piece of code, a list of comma delimited values will be obtained from any column that you need.

 
DECLARE @SpreadingCommaDelimitedValues nvarchar(max)	
DECLARE @ColumnValuesToXml xml;
SELECT @ColumnValuesToXml = (
	  SELECT QuoteName([Spreading Column]) + ','
	  FROM [Table or Query]
	  FOR XML PATH);	 
SELECT @SpreadingValues = @ColumnValuesToXml.value('.','nvarchar(max)');
SET @SpreadingCommaDelimitedValues =SUBSTRING(@SpreadingCommaDelimitedValues,0,len(@SpreadingCommaDelimitedValues))

If you apply the previous code to ShipCountry field you’ll obtain a value that will look like to ([Spain],[United States], [France], ...)

Finally, I will use the same database explained before in my article Generating Random Int SQL Values between Upper and Lower Limits. So, I suggest you have a look to understand the tables scheme used to write the SQL queries. Anyway, you can download the full source code from this post.

3. USING THE CODE

Let's get straight to the point! Here it is the code to run queries. You can find explanations along the code as well.

if (OBJECT_ID('tempdb..#cteSales')) is not null
begin
	drop table #cteSales
end

declare @sql nvarchar(2000)
declare @spreadingValues nvarchar(200)
	
;with cteSales as
(select Sales.Id, 
	SalesDetails.SalesDetailId, 
	Sales.OrderDate, 
	cast(year(OrderDate) as CHAR(4)) 
		+ '-' + replicate('0',2-len(cast(month(OrderDate)as char(2)))) 
		+ cast(month(OrderDate)as char(2)) as OrderYearMonthDate, /* Format YYYY-MM */
	concat(Emp.FName,' ',Emp.LName) as Employee, 
	concat(Cus.FName,' ',Cus.LName) as Customer, 
	Cou.CountryName as CustomerCountry, 
	Pro.Model as ProductModel, 
	SalesDetails.Quantity, 
	Pro.Cost as UnitCost,
	SalesDetails.Quantity * Pro.Cost as TotalCost
from Sales
	join Employees Emp on Emp.EmployeeId=Sales.EmployeeId
	join Customers Cus on Cus.CustomerId=Sales.CustomerId
	join Countries Cou on Cou.CountryId=Cus.CountryId
	join SalesDetails on
		SalesDetails.SalesId=Sales.Id
	join Products Pro on
		Pro.ProductId=SalesDetails.ProductId 
)
/* Load temp table with result set from CTE expression */
select * into #cteSales from cteSales

/* Example 1. STATIC query: Get data grouped by [EMPLOYEE, COUNTRY] and spreading by [COUNTRY]  */
;with cte1 as (select Employee, CustomerCountry /* spreading Column */,  Quantity /* Aggregate value */
from #cteSales)
select Employee, Australia, Germany, Spain, India, [United States]
from cte1
pivot (sum(Quantity) for CustomerCountry in (Australia, Germany, Spain, India, [United States])) as R	
	
/* End static Query */


/* Example 2. DYNAMIC query: Get data grouped by [EMPLOYEE, COUNTRY] 
  and spreading by [COUNTRY]  */
/* Same result as previous example */
/* Quit comment to test */
	--DECLARE @CountryNamesToXml xml; 
	--SELECT @CountryNamesToXml = (
	--  SELECT '[' + CountryName + '],'
	--  FROM Countries
	--  FOR XML PATH);
	 
	--SELECT @spreadingValues = @CountryNamesToXml.value('.','nvarchar(200)');
	--set @spreadingValues=SUBSTRING(@spreadingValues,0,len(@spreadingValues))

	--set @sql = ';with cte1 as (select Employee, CustomerCountry, Quantity
	--from #cteSales)
	--select Employee,' + @spreadingValues + '
	--from cte1
	--pivot (sum(Quantity) for CustomerCountry in (' + @spreadingValues + ')) as R'
	--exec (@sql)
	
/* End dynamic Query */

/* Example 3. DYNAMIC query: Get data grouped by [EMPLOYEE, YEAR-MONTH]   and spreading by [YEAR-MONTH]  */
/* This example try to solve a question in this site that I saw some days ago */

	DECLARE @MonthYearToXml xml; 
	SELECT @MonthYearToXml = (
	  SELECT distinct quoteName(OrderYearMonthDate)+','
	  FROM #cteSales
	  order by quoteName(OrderYearMonthDate)+','
	  FOR XML PATH);
	 
	SELECT @spreadingValues = @MonthYearToXml.value('.','nvarchar(200)');
	set @spreadingValues=SUBSTRING(@spreadingValues,0,len(@spreadingValues))

	set @sql = ';with cte1 as (select Employee, OrderYearMonthDate, Quantity
	from #cteSales)
	select Employee,' + @spreadingValues + '
	from cte1
	pivot (sum(Quantity) for OrderYearMonthDate in (' + @spreadingValues + ')) as R'
	exec (@sql)
/* End dynamic Query */

/* Example 4. DYNAMIC query: Get data grouped by [EMPLOYEE, COUNTRY]   and spreading by [COUNTRY]  */

	DECLARE @ProductNamesToXml xml; 
	SELECT @ProductNamesToXml = (
	  SELECT quoteName(Model) + ','
	  FROM Products
	  FOR XML PATH);
	 
	SELECT @spreadingValues = @ProductNamesToXml.value('.','nvarchar(200)');
	set @spreadingValues=SUBSTRING(@spreadingValues,0,len(@spreadingValues))

	set @sql = ';with cte1 as (select Employee, ProductModel, Quantity
	from #cteSales)
	select Employee,' + @spreadingValues + '
	from cte1
	pivot (sum(Quantity) for ProductModel in (' + @spreadingValues + ')) as R'
	exec (@sql)
/* End dynamic Query */

/* Cleans temp data */
drop table #cteSales

Once shown the code, let's have a look to Source Query and output Result Sets using PIVOT.

Source Query

It is built using a CTE (Common Table Expression) to show all the sales details stored in the database. As you can see, it returns information about Sales, Employees, Customers, Countries, quantity and cost of ordered items, etc. All examples will use column Quantity as Aggregated Column with SUM as operator and Employee column as Non-Pivoted Column in the final results. This way, results will show sum of quantity of products sold by each employee according to different cases of Spreading Column (Countries, a combination of Month and Year and Products)

Example 1

It shows sum of quantity of products sold by each employee for each country. Employee is the Non-Pivoted Column, CustomerCountry is the Spreading Column and Quantity is the Aggregated Column.

Example 2

The same as previous example but using dynamic SQL to build Spreading Column. This kind of query help us to query all the values in the Spreading Column and therefore you don't have to write statically the spreading values to be converted into new header columns.

Example 3

It shows sum of quantity of products sold by each employee for each month taking the pattern "MM-YYYY" to create the Spreading Column. As in previous examples, Employee is the Non-Pivoted Column and Quantity is the Aggregated Column.

Example 4

It shows the sum of quantity of products sold by each employee for each product model. It uses dynamic SQL, too. Employee is the Non-Pivoted Column, ProductModelis the Spreading Column and Quantity is the Aggregated Column.

4. ENVIRONMENT

This article has been tested using Microsoft SQL Server 2012 SP3.

5. REFERENCES

Microsoft TechNet Using PIVOT and UNPIVOT

Tags :

Add comment