Click here to Skip to main content
15,867,308 members
Articles / Programming Languages / SQL

Client Side Multi-Column Dynamic Pivot

Rate me:
Please Sign up or sign in to vote.
5.00/5 (20 votes)
15 Jul 2014Apache9 min read 43.4K   1.5K   27   8
This article introduces you to client side multi-column dynamic pivot, a cleaner, simpler and elegant alternative to the complex and convoluted dynamic server side pivot.

Pivot form

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:

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

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

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

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

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

SQL
<#@ 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:

C#
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:

C#
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:

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

SQL
<#@ 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:

C#
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:

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

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

C#
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:

SQL
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.

C#
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:

C#
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.

License

This article, along with any associated source code and files, is licensed under The Apache License, Version 2.0


Written By
Architect i-nercya intelligent software
Spain Spain
Working on i-nercya intelligent software as a Database Administrator, IT Manager and Software Architect.

I developed my first programs 30 years ago in BASIC on a CASIO PB 100 programmable calculator which had 1.5 Kb of RAM (with extended memory, factory version had 512 bytes of RAM). Later I programmed on a Commodore 64 micro computer, data was stored on a music-like magnetic tape. Oh My!

I worked for the Spanish Air Force, I have a master degree on aeronautical engineering after all. But making software is my passion.

My other passions are dancing tango and ballroom, cooking good food and watching cinema. And of course and mainly, to love my girlfriend and my daughter.

I also worked on Solid Quality Mentors where my job was SQL Server consulting. I optimized lot of queries on that job. I also worked teaching SQL Server.

I was a Visual Basic MVP during three years several years ago. But I think I don't remember VB sintax anymore, c# is my development language now.

Comments and Discussions

 
GeneralMy vote of 5 Pin
Antonio Nakić Alfirević20-Nov-14 4:11
Antonio Nakić Alfirević20-Nov-14 4:11 
GeneralMy vote of 5 Pin
Ștefan-Mihai MOGA13-Aug-14 1:41
professionalȘtefan-Mihai MOGA13-Aug-14 1:41 
GeneralRe: My vote of 5 Pin
Jesús López Méndez13-Aug-14 8:46
Jesús López Méndez13-Aug-14 8:46 
GeneralMy vote of 5 Pin
khanhamid92215-Jul-14 12:05
khanhamid92215-Jul-14 12:05 
QuestionGood candidate for next month Database competition Pin
thatraja15-Jul-14 5:43
professionalthatraja15-Jul-14 5:43 
AnswerRe: Good candidate for next month Database competition Pin
Jesús López Méndez15-Jul-14 7:48
Jesús López Méndez15-Jul-14 7:48 
GeneralRe: Good candidate for next month Database competition Pin
thatraja15-Jul-14 21:09
professionalthatraja15-Jul-14 21:09 
GeneralRe: Good candidate for next month Database competition Pin
Jesús López Méndez16-Jul-14 7:28
Jesús López Méndez16-Jul-14 7:28 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.