Introduction
Several relational database management systems such as SQL Server and ORACLE support the PIVOT operator. Other database systems do not, however the same result can be achieved using CASE expressions but with more complex syntax. Unfortunately, both approaches have several shortcomings:
- Writing a query that performs static
PIVOT is relatively easy, but both approaches require aggregation and many PIVOT use cases do not, wasting computer resources performing superfluous aggregation. - To perform multi-column
PIVOT, you need to resort to CASE expressions because the PIVOT operator supports only one pivot column. - Things get more complex and convoluted when you need dynamic
PIVOT. You have to dynamically build the SQL statement, concatenate column values from multiple rows and execute more than one query.
Q. So, what can I do to overcome these shortcomings?
A. Simple: Do pivot at client side.
This article explains how to perform client side multi-column dynamic pivot providing source code and samples. You will see that it is very easy, clean and elegant. But before entering into client side pivot, I'm going to provide you some background. If you already have the background, you can go directly to client side pivot section.
Setting Up the Sample Application
The source code includes a modified version of Northwind database that you will need to attach to run the samples included in this article. Please connect to (localdb)\v11.0 and execute the following SQL statement. Change the MDF file path as appropriate:
CREATE DATABASE Northwind
ON (FILENAME = 'C:\Projects\ClientSidePivot\Northwind.mdf')
FOR ATTACH;
The source code also includes a Visual Studio 2013 solution that contains a class library project called ClientSidePivot and a Windows Forms application called WinClientSidePivot. To build the solution, you will need to download Nuget.exe from here and place it into .nuget subfolder of the solution folder.
If you attach the database to SQL Server instance other than (localdb)\v11.0, please change the connection string in the application configuration file as appropriate.
Background
To better understand sever side PIVOT, you might want to read the following:
What is Pivot
People usually think of pivot as the operation that transforms rows to columns. But more accurately, pivot is an operation that transforms column values to columns.
SQL Server Books Online define PIVOT as follows: PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output.
Usually, a pivot operation involves aggregation but sometimes aggregation is not what you want. For example, given the following result set, which is already aggregated:
| Product | Year | Sales | Orders |
| Chai | 1997 | 4887.00 € | 16 |
| Chai | 1998 | 6295.50 € | 16 |
| Chang | 1997 | 7038.55 € | 18 |
| Chang | 1998 | 6299.45 € | 18 |
| Aniseed Syrup | 1997 | 1724.00 € | 7 |
| Aniseed Syrup | 1998 | 1080.00 € | 4 |
You might want to perform a multi-column pivot operation to transform the above resultset into the following form:
| Product | Y1997Sales | Y1998Sales | Y1997Orders | Y1998Orders |
| Chai | 4887.00 € | 6295.50 € | 16 | 16 |
| Chang | 7038.55 € | 6299.45 € | 18 | 18 |
| Aniseed Syrup | 1724.00 € | 1080.00 € | 7 | 4 |
Note that the transformation rotates Sales and Orders columns around the Year column. In this case, the Year column is the pivot column, and Sales and Orders are value columns. Product is an unpivoted column. Tuples of unpivoted columns are unique in the transformed resultset (pivoted table). Y1996Sales, Y1998Sales, Y1997Orders and Y1998Orders are pivoted columns, their name are derived from the pivot column values (the Year column values in this case).
When you know in advance the values of the pivot column, then you perform static pivot, when you don't know them until runtime then you need to resort to dynamic pivot.
Static Pivot Using PIVOT Operator
There are lots of examples on the web about the PIVOT operator, but an example is included here just for convenience. The following query:
WITH S
AS
(
SELECT
P.ProductID, P.ProductName,
'Y' + CAST(DATEPART(year, O.OrderDate) as varchar) + 'Sales' AS SalesYear,
OD.Quantity * OD.UnitPrice * (1 - OD.Discount) AS Sales
FROM
dbo.Products P
LEFT OUTER JOIN
(
dbo.Orders O
INNER JOIN dbo.OrderDetails OD
ON O.OrderID = OD.OrderID
) ON P.ProductID = OD.ProductID
WHERE
P.ProductID IN (1, 2, 3)
AND DATEPART(year, O.OrderDate) IN (1997, 1998)
)
SELECT PV.ProductName, PV.Y1997Sales, PV.Y1998Sales
FROM S
PIVOT
(
SUM(Sales)
FOR SalesYear IN (Y1997Sales, Y1998Sales)
) AS PV
produces the following result set:
| Product | Y1997Sales | Y1998Sales |
| Chai | 4887.00 € | 6295.50 € |
| Chang | 7038.55 € | 6299.45 € |
| Aniseed Syrup | 1724.00 € | 1080.00 € |
The syntax of the above query can be schematized as follows:
WITH S
AS
(
<source data query>
)
SELECT UnpivotedColumn1, UnpivotedColumn2, ..., PivotedColumn1, PivotedColumn2, ...
FROM S
PIVOT
(
AggregateFunction(ValueColumn)
FOR PivotColumn IN (PivotedColumn1, PivotedColumn2, ....)
) AS PV
Static Pivot Using Case Expressions
When your database doesn't support the PIVOT operator or you need multi-column pivot, you need to resort to CASE expressions.
This query:
WITH S
AS
(
SELECT
P.ProductID, P.ProductName,
DATEPART(year, O.OrderDate) AS SalesYear,
OD.Quantity * OD.UnitPrice * (1 - OD.Discount) AS Sales
FROM
dbo.Products P
LEFT OUTER JOIN
(
dbo.Orders O
INNER JOIN dbo.OrderDetails OD
ON O.OrderID = OD.OrderID
) ON P.ProductID = OD.ProductID
WHERE
P.ProductID IN (1, 2, 3)
AND DATEPART(year, O.OrderDate) IN (1997, 1998)
)
SELECT
S.ProductName,
SUM(CASE WHEN S.SalesYear = 1997 THEN S.Sales ELSE 0 END) AS Y1997Sales,
SUM(CASE WHEN S.SalesYear = 1998 THEN S.Sales ELSE 0 END) AS Y1998Sales,
SUM(CASE WHEN S.SalesYear = 1997 THEN 1 ELSE 0 END) AS Y1997Orders,
SUM(CASE WHEN S.SalesYear = 1998 THEN 1 ELSE 0 END) AS Y1998Orders
FROM S
GROUP BY
S.ProductID, S.ProductName
returns the following resultset:
| Product | Y1997Sales | Y1998Sales | Y1997Orders | Y1998Orders |
| Chai | 4887.00 € | 6295.50 € | 16 | 16 |
| Chang | 7038.55 € | 6299.45 € | 18 | 18 |
| Aniseed Syrup | 1724.00 € | 1080.00 € | 7 | 4 |
Dynamic Pivot Using PIVOT Operator
When you don't know the pivot column values until runtime, you need to resort to dynamic pivot. In other words, you need to build the select statement at runtime. If you need to pivot just one column, and your database supports it, you can use the PIVOT operator.
To build the pivot select statement, you need to figure out the pivot column values, therefore you need to execute one additional query to get those values. The following query does it:
SELECT DISTINCT DATEPART(year, OrderDate) AS SalesYear
FROM dbo.Orders
ORDER BY DATEPART(year, OrderDate)
Once you have the pivot column values, you need to concatenate them to get a comma separate list of column names and insert them into the right places in the pivot select statement.
Most examples on the Web use server side dynamic SQL to implement dynamic pivot, therefore including such an example in this article would not make much sense. A client side approach is provided instead. The sample application (a Windows Forms app) builds the pivot select statement at runtime using a Runtime Text Template.
The following is the content of DynamicPivotUsingPivotOperatorStatementTemplate.tt runtime text template file:
<#@ template language="C#" #>
<#@ assembly name="System.Core" #>
WITH S
AS
(
SELECT
P.ProductID, P.ProductName,
'Y' + CAST(DATEPART(year, O.OrderDate) as varchar) + 'Sales' AS SalesYear,
OD.Quantity * OD.UnitPrice * (1 - OD.Discount) AS Sales
FROM
dbo.Products P
LEFT OUTER JOIN
(
dbo.Orders O
INNER JOIN dbo.OrderDetails OD
ON O.OrderID = OD.OrderID
) ON P.ProductID = OD.ProductID
)
SELECT ProductName, <#= PivotedColumNames #>
FROM S
PIVOT
(
SUM(Sales)
FOR SalesYear IN (<#= PivotedColumNames #>)
) AS PV
PivotedColumNames is a private field defined in the DynamicPivotUsingPivotOperatorStatementTemplate.partial.cs code file:
using System;
using System.Collections.Generic;
using System.Linq;
namespace WinClientSidePivot
{
public partial class DynamicPivotUsingPivotOperatorStatementTemplate
{
private IEnumerable<int> _salesYears;
public IEnumerable<int> SalesYears
{
get { return _salesYears; }
set { _salesYears = value; SetPivotedColumnNames(); }
}
private void SetPivotedColumnNames()
{
var names = _salesYears
.Select(x => "Y" + x.ToString() + "Sales");
PivotedColumNames = string.Join(", ", names);
}
private string PivotedColumNames;
}
}
The sample application uses the following code to execute the query:
private IEnumerable<int> GetDistinctSalesYears()
{
this.EnsureOpenConnection();
using(var cmd = CreateCommand(Properties.Resources.DistinctSalesYearsSelectStatement))
using(var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
yield return reader.GetInt32(0);
}
}
}
public DataTable ExecuteDynamicPivotUsingPivotOperator()
{
var template = new DynamicPivotUsingPivotOperatorStatementTemplate
{
SalesYears = GetDistinctSalesYears()
};
return GetDataTableFromSelectStatement(template.TransformText());
}
EnsureOpenConnection, CreateCommand and GetDataTableFromSelectStatement methods are defined in the sample application. They use ADO.NET standard code. They are not included in this article to save space and to avoid noise.
Building the SQL statement at the client side makes dynamic server side pivot less convoluted, because to manipulate strings, C# is a much better language than SQL, and Runtime Text Templates help a lot.
Building the SQL statement at the server side requires string concatenation, and to get the pivoted column list, you need to resort to tricks such as the use of FOR XML clause and STUFF function to concatenate the pivot column values.
Dynamic Pivot Using CASE Expressions
When you don't know what the pivot column values are until runtime, you need to build the select statement at runtime. If you need to pivot more than one column, or your database doesn't support the PIVOT operator, you need to perform dynamic pivot using CASE expressions.
To build the pivot select statement, in the first place, you need to figure out the pivot column values, therefore you need to execute one additional query to get those values. The following query does it:
SELECT DISTINCT DATEPART(year, OrderDate) AS SalesYear
FROM dbo.Orders
ORDER BY DATEPART(year, OrderDate)
Once you have the pivot column values, you need to build one CASE expression for each pivot column value and value column, then concatenate them and to insert them into the right places in the pivot select statement.
Again, instead of using server side dynamic SQL, this article uses a client side approach. The sample application builds the pivot select statement at runtime using a Runtime Text Template.
The following is the content of DynamicPivotUsingCaseExpressionsStatementTemplate.tt runtime text template file:
<#@ template language="C#" #>
<#@ assembly name="System.Core" #>
WITH S
AS
(
SELECT
P.ProductID, P.ProductName,
DATEPART(year, O.OrderDate) AS SalesYear,
OD.Quantity * OD.UnitPrice * (1 - OD.Discount) AS Sales
FROM
dbo.Products P
LEFT OUTER JOIN
(
dbo.Orders O
INNER JOIN dbo.OrderDetails OD
ON O.OrderID = OD.OrderID
) ON P.ProductID = OD.ProductID
)
SELECT
S.ProductName
<# foreach (var salesYear in SalesYears) { #>
, SUM(CASE WHEN S.SalesYear = <#= salesYear #> THEN S.Sales ELSE 0 END) AS Y<#= salesYear #>Sales
<# }
foreach (var salesYear in SalesYears) { #>
, SUM(CASE WHEN S.SalesYear = <#= salesYear #> THEN 1 ELSE 0 END) AS Y<#= salesYear #>Orders
<# } #>
FROM S
GROUP BY
S.ProductID, S.ProductName
ORDER BY
S.ProductName
The sample application uses the following code to execute the query:
public DataTable ExecuteDynamicPivotUsingCaseExpressions()
{
var template = new DynamicPivotUsingCaseExpressionsStatementTemplate
{
SalesYears = GetDistinctSalesYears().ToList()
};
return GetDataTableFromSelectStatement(template.TransformText());
}
Again, building the SQL statement at the client side makes dynamic server side pivot simpler, because to manipulate strings, C# is a much better language than SQL, and Runtime Text Templates help a lot.
Building the SQL statement at the server side requires string concatenation, and to get the case expressions concatenated, you need to resort to tricks such as the use of FOR XML clause and STUFF function.
Client Side Pivot
This article provides you a neat, simple and elegant implementation of multi-column dynamic client side pivot. Two approaches are provided:
Client Side Pivot Using EntityLite
EntityLite is a lightweight, database first, micro ORM. Please read this article for an introduction to EntityLite.
In EntityLite, to perform client side pivot, you call the Pivot method on the QueryLite object. The Pivot method does not perform any aggregation, it just executes the query and rotates value columns around pivot columns. If you need to aggregate data, do it inside the query. So, in EntityLite, if aggregation is required, it is done at server side, and the rotation is done at client side. The Pivot method returns a DataTable object. It returns a DataTable object instead of a collection of entities because it is dynamic pivot, pivoted columns are not known until runtime, therefore DataTable seems to be a good choice.
Given the following entity view that aggregates sales data by year:
CREATE VIEW [dbo].[ProductSale_Year]
AS
SELECT
P.ProductID, P.ProductName,
DATEPART(year, O.OrderDate) AS [Year],
SUM(OD.Quantity * OD.UnitPrice * (1 - OD.Discount)) AS Sales,
COUNT(*) AS Orders
FROM
dbo.Products P
LEFT OUTER JOIN
(
dbo.Orders O
INNER JOIN dbo.OrderDetails OD
ON O.OrderID = OD.OrderID
) ON P.ProductID = OD.ProductID
GROUP BY
P.ProductID, P.ProductName,
DATEPART(year, O.OrderDate)
You can execute a query based on that entity view and rotate Sales and Orders columns around Year column using the following code:
DataTable pivotedSales = this.DataService
.ProductSaleRepository
.Query("Year")
.Where(ProductSaleFields.ProductId, OperatorLite.In, new int[] { 1, 2, 3})
.And(ProductSaleFields.Year, OperatorLite.In, new int[] { 1997, 1998 })
.OrderBy(ProductSaleFields.ProductName)
.Pivot
(
new PivotTransform
{
PivotColumnName = ProductSaleFields.Year,
ValueColumnName = ProductSaleFields.Sales,
GetPivotedColumnName = year => "Y" + year.ToString() + "Sales"
},
new PivotTransform
{
PivotColumnName = ProductSaleFields.Year,
ValueColumnName = ProductSaleFields.Orders,
GetPivotedColumnName = year => "Y" + year.ToString() + "Orders"
}
);
Note that the query is ordered by the ProductName field. This is because the Pivot method requires the query to be ordered by all unpivoted columns. This is also the way you specify unpivoted columns, you include them in the ORDER BY clause of the query.
GetPivotedColumnName is a function that takes the pivot column value as the argument and it must return the corresponding pivoted column name. If not specified, the pivoted column name is the pivot column value (PivotColumnValue.ToString())
By default, pivoted columns are ordered by the PivotTransformIndex (the order in which PivotTransform objects are written) and then by the pivot column value.
The following table shows you the content of pivotedSales DataTable:
| Product | Y1997Sales | Y1998Sales | Y1997Orders | Y1998Orders |
| Aniseed Syrup | 1724.00 € | 1080.00 € | 7 | 4 |
| Chai | 4887.00 € | 6295.50 € | 16 | 16 |
| Chang | 7038.55 € | 6299.45 € | 18 | 18 |
If the default pivoted column order is not what you want, you can specify a custom order by including the pivotedColumnComparison argument:
DataTable pivotedSales = this.DataService
.ProductSaleRepository
.Query("Year")
.Where(ProductSaleFields.ProductId, OperatorLite.In, new int[] { 1, 2, 3})
.And(ProductSaleFields.Year, OperatorLite.In, new int[] { 1997, 1998 })
.OrderBy(ProductSaleFields.ProductName)
.Pivot
(
(c1, c2) =>
{
int yearComp = ((int)c1.PivotColumnValue).CompareTo(c2.PivotColumnValue);
if (yearComp != 0) return yearComp;
return c1.PivotTransformIndex.CompareTo(c2.PivotTransformIndex);
},
new PivotTransform
{
PivotColumnName = ProductSaleFields.Year,
ValueColumnName = ProductSaleFields.Sales,
GetPivotedColumnName = year => "Y" + year.ToString() + "Sales"
},
new PivotTransform
{
PivotColumnName = ProductSaleFields.Year,
ValueColumnName = ProductSaleFields.Orders,
GetPivotedColumnName = year => "Y" + year.ToString() + "Orders"
}
);
The following is the result:
| Product | Y1997Sales | Y1997Orders | Y1998Sales | Y1998Orders |
| Aniseed Syrup | 1724.00 € | 7 | 1080.00 € | 4 |
| Chai | 4887.00 € | 16 | 6295.50 € | 16 |
| Chang | 7038.55 € | 18 | 6299.45 € | 18 |
Client Side Pivot Using Direct ADO.NET
The sample code includes ClientSidePivot a class library that implements the Pivot extension method. So, to perform client side pivot, you don't need to adopt EntityLite if you don't want. The code is almost a exact copy of the EntityLite pivot implementation.
Given the following PivotSalesQuery query:
SELECT
ProductName, "Year", Sales, Orders
FROM
dbo.ProductSale_Year
ORDER BY
ProductName
The sample application uses the following code to execute it and to perform client side pivot with default pivoted column order.
public DataTable ExecuteDirectAdoNetClientSidePivotDefaultOrder()
{
this.EnsureOpenConnection();
using (var cmd = this.CreateCommand(Properties.Resources.ProductSalesQuery))
using (var reader = cmd.ExecuteReader())
{
return reader.Pivot
(
new PivotDef
{
UnpivotedColumnNames = new string[] { "ProductName"},
PivotTransforms = new PivotTransform[] {
new PivotTransform
{
PivotColumnName ="Year",
ValueColumnName = "Sales",
GetPivotedColumnName = year => "Y" + year.ToString() + "Sales"
},
new PivotTransform
{
PivotColumnName = "Year",
ValueColumnName = "Orders",
GetPivotedColumnName = year => "Y" + year.ToString() + "Orders"
}
}
}
);
}
}
If the default pivoted column order is not what you want, you can specify a custom order:
public DataTable ExecuteDirectAdoNetClientSidePivotCustomOrder()
{
this.EnsureOpenConnection();
using (var cmd = this.CreateCommand(Properties.Resources.ProductSalesQuery))
using (var reader = cmd.ExecuteReader())
{
return reader.Pivot
(
new PivotDef
{
UnpivotedColumnNames = new string[] { "ProductName" },
PivotTransforms = new PivotTransform[] {
new PivotTransform
{
PivotColumnName ="Year",
ValueColumnName = "Sales",
GetPivotedColumnName = year => "Y" + year.ToString() + "Sales"
},
new PivotTransform
{
PivotColumnName = "Year",
ValueColumnName = "Orders",
GetPivotedColumnName = year => "Y" + year.ToString() + "Orders"
}
}
},
(c1, c2) =>
{
int yearComp = ((int)c1.PivotColumnValue).CompareTo(c2.PivotColumnValue);
if (yearComp != 0) return yearComp;
return c1.PivotTransformIndex.CompareTo(c2.PivotTransformIndex);
}
);
}
}
Conclusion
There are two ways to perform server side dynamic pivot: the PIVOT operator and CASE expressions, both require dynamic SQL, and you can choose between client side dynamic SQL and server side dynamic SQL. But client side pivot is probably a better alternative than server side dynamic pivot.
