客户端多列动态透视

• 下载源代码 - 751 KB

引言

一些关系数据库管理系统（如 SQL Server 和 ORACLE）支持 PIVOT 运算符。其他数据库系统则不支持，但可以使用 CASE 表达式实现相同的结果，但语法更复杂。不幸的是，这两种方法都有几个缺点

• 编写执行静态 PIVOT 的查询相对容易，但这两种方法都需要聚合，并且许多 PIVOT 用例不需要聚合，从而浪费计算机资源执行不必要的聚合。
• 要执行多列 PIVOT，您需要借助 CASE 表达式，因为 PIVOT 运算符只支持一个透视列。
• 当您需要动态 PIVOT 时，事情变得更加复杂和繁琐。您必须动态构建 SQL 语句，连接多行中的列值，并执行多个查询。

问：那么，我该如何克服这些缺点呢？

答：很简单：在客户端执行透视。

本文解释了如何执行客户端多列动态透视，并提供了源代码和示例。您将看到它非常容易、清晰和优雅。但在进入客户端透视之前，我将为您提供一些背景知识。如果您已经了解背景知识，可以直接跳到客户端透视部分。

设置示例应用程序

源代码包含 Northwind 数据库的修改版本，您需要将其附加才能运行本文中包含的示例。请连接到 (localdb)\v11.0 并执行以下 SQL 语句。根据需要更改 MDF 文件路径

CREATE DATABASE Northwind
ON (FILENAME = 'C:\Projects\ClientSidePivot\Northwind.mdf') 
FOR ATTACH;    

源代码还包含一个 Visual Studio 2013 解决方案，其中包含一个名为 ClientSidePivot 的类库项目和一个名为 WinClientSidePivot 的 Windows Forms 应用程序。要构建解决方案，您需要从此处下载 Nuget.exe 并将其放置到解决方案文件夹的 .nuget 子文件夹中。

如果您将数据库附加到 (localdb)\v11.0 以外的 SQL Server 实例，请根据需要更改应用程序配置文件中的连接字符串。

背景

为了更好地理解服务器端 PIVOT，您可能需要阅读以下内容

• 什么是透视
• 使用 PIVOT 运算符进行静态透视
• 使用 CASE 表达式进行静态透视
• 使用 PIVOT 运算符进行动态透视
• 使用 CASE 表达式进行动态透视

什么是透视

人们通常认为透视是将行转换为列的操作。但更准确地说，透视 是将列值转换为列的操作。

SQL Server 联机丛书对 PIVOT 的定义如下：PIVOT 通过将表达式中某一列的唯一值转换为输出中的多列来旋转表值表达式，并在最终输出中需要的任何剩余列值上执行聚合。

通常，透视 操作涉及聚合，但有时聚合不是您想要的。例如，给定以下已经聚合的结果集

产品	年份	销售	订单
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

您可能希望执行多列 透视 操作，将上述结果集转换为以下形式

产品	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

请注意，该转换围绕 Year 列旋转 Sales 和 Orders 列。在这种情况下，Year 列是 透视 列，Sales 和 Orders 是值列。Product 是未透视列。未透视列的元组在转换后的结果集（透视表）中是唯一的。Y1996Sales、Y1998Sales、Y1997Orders 和 Y1998Orders 是透视列，它们的名称派生自 透视 列值（在本例中为 Year 列值）。

当您预先知道 透视 列的值时，您执行静态 透视；当您直到运行时才知道它们时，您需要借助动态 透视。

使用 PIVOT 运算符进行静态透视

网上有大量关于 PIVOT 运算符的示例，但这里为了方便起见包含一个示例。以下查询

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    

产生以下结果集

产品	Y1997Sales	Y1998Sales
Chai	4887.00 €	6295.50 €
Chang	7038.55 €	6299.45 €
Aniseed Syrup	1724.00 €	1080.00 €

上述查询的语法可以图示如下

WITH S
AS
(
    <source data query>
)
SELECT UnpivotedColumn1, UnpivotedColumn2, ..., PivotedColumn1, PivotedColumn2, ...
FROM S
PIVOT 
( 
    AggregateFunction(ValueColumn)
    FOR PivotColumn IN (PivotedColumn1, PivotedColumn2, ....)
) AS PV    

使用 Case 表达式进行静态透视

当您的数据库不支持 PIVOT 运算符或您需要多列 透视 时，您需要借助 CASE 表达式。

此查询

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

返回以下结果集

产品	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

使用 PIVOT 运算符进行动态透视

当您直到运行时才知道 透视 列值时，您需要借助动态 透视。换句话说，您需要在运行时构建 select 语句。如果您只需要 透视 一列，并且您的数据库支持它，您可以使用 PIVOT 运算符。

要构建 透视 select 语句，您需要找出 透视 列值，因此您需要执行一个额外的查询来获取这些值。以下查询可以做到

SELECT DISTINCT DATEPART(year, OrderDate) AS SalesYear 
FROM dbo.Orders 
ORDER BY DATEPART(year, OrderDate)

一旦您有了 透视 列值，您需要将它们连接起来以获得一个逗号分隔的列名列表，并将它们插入到 透视 select 语句的正确位置。

Web 上的大多数示例都使用服务器端动态 SQL 来实现动态 透视，因此在本文中包含这样的示例没有多大意义。相反，本文提供了一种客户端方法。示例应用程序（一个 Windows Forms 应用程序）在运行时使用 运行时文本模板 构建 透视 select 语句。

以下是 DynamicPivotUsingPivotOperatorStatementTemplate.tt 运行时文本模板文件的内容

<#@ 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 是在 DynamicPivotUsingPivotOperatorStatementTemplate.partial.cs 代码文件中定义的 private 字段

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

示例应用程序使用以下代码执行查询

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 和 GetDataTableFromSelectStatement 方法在示例应用程序中定义。它们使用 ADO.NET 标准代码。为了节省空间和避免干扰，它们未包含在本文中。

在客户端构建 SQL 语句使动态服务器端透视不那么复杂，因为对于字符串操作，C# 比 SQL 语言更好，并且 运行时文本模板 帮助很大。

在服务器端构建 SQL 语句需要 string 连接，并且为了获得透视列列表，您需要借助技巧，例如使用 FOR XML 子句和 STUFF 函数来连接 透视 列值。

使用 CASE 表达式进行动态透视

当您直到运行时才知道 透视 列值时，您需要在运行时构建 select 语句。如果您需要 透视 多于一列，或者您的数据库不支持 PIVOT 运算符，您需要使用 CASE 表达式执行动态 透视。

要构建 透视 select 语句，首先，您需要找出 透视 列值，因此您需要执行一个额外的查询来获取这些值。以下查询可以做到

SELECT DISTINCT DATEPART(year, OrderDate) AS SalesYear 
FROM dbo.Orders 
ORDER BY DATEPART(year, OrderDate)

一旦您有了 透视 列值，您需要为每个 透视 列值和值列构建一个 CASE 表达式，然后将它们连接起来并插入到 透视 select 语句的正确位置。

同样，本文没有使用服务器端动态 SQL，而是使用客户端方法。示例应用程序在运行时使用 运行时文本模板 构建 透视 select 语句。

以下是 DynamicPivotUsingCaseExpressionsStatementTemplate.tt 运行时文本模板文件的内容

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

示例应用程序使用以下代码执行查询

public DataTable ExecuteDynamicPivotUsingCaseExpressions()
{
    var template = new DynamicPivotUsingCaseExpressionsStatementTemplate
    {
        SalesYears = GetDistinctSalesYears().ToList()
    };
    return GetDataTableFromSelectStatement(template.TransformText());
}

同样，在客户端构建 SQL 语句使动态服务器端 透视 更简单，因为对于字符串操作，C# 比 SQL 语言更好，并且 运行时文本模板 帮助很大。

在服务器端构建 SQL 语句需要 string 连接，并且为了获得连接的 case 表达式，您需要借助技巧，例如使用 FOR XML 子句和 STUFF 函数。

客户端透视

本文为您提供了多列动态客户端透视的一种简洁、简单且优雅的实现。提供了两种方法

• 使用 EntityLite 进行客户端透视
• 使用直接 ADO.NET 进行客户端透视

使用 EntityLite 进行客户端透视

EntityLite 是一个轻量级的、数据库优先的微型 ORM。请阅读本文以了解 EntityLite 的介绍。

在 EntityLite 中，要执行客户端透视，您可以在 QueryLite 对象上调用 Pivot 方法。Pivot 方法不执行任何聚合，它只是执行查询并围绕 透视 列旋转值列。如果您需要聚合数据，请在查询内部进行。因此，在 EntityLite 中，如果需要聚合，则在服务器端完成，而旋转在客户端完成。Pivot 方法返回一个 DataTable 对象。它返回一个 DataTable 对象而不是实体集合，因为它是动态 透视，透视列直到运行时才知道，因此 DataTable 似乎是一个不错的选择。

给定以下按年份聚合销售数据的实体视图

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)

您可以使用以下代码执行基于该实体视图的查询，并围绕 Year 列旋转 Sales 和 Orders 列

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

请注意，查询按 ProductName 字段排序。这是因为 Pivot 方法要求查询按所有未透视列排序。这也是您指定未透视列的方式，将它们包含在查询的 ORDER BY 子句中。

GetPivotedColumnName 是一个函数，它以 透视 列值为参数，并且必须返回相应的透视列名。如果未指定，则透视列名是 透视 列值（PivotColumnValue.ToString()）

默认情况下，透视列按 PivotTransformIndex（PivotTransform 对象的写入顺序）排序，然后按 透视 列值排序。

下表显示了 pivotedSales DataTable 的内容

产品	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

如果默认的透视列顺序不是您想要的，您可以通过包含 pivotedColumnComparison 参数来指定自定义顺序

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

结果如下

产品	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

使用直接 ADO.NET 进行客户端透视

示例代码包括 ClientSidePivot 类库，它实现了 Pivot 扩展方法。因此，如果您不想采用 EntityLite，您也不需要采用。该代码几乎是 EntityLite 透视 实现的精确副本。

给定以下 PivotSalesQuery 查询

SELECT
    ProductName, "Year", Sales, Orders
FROM 
    dbo.ProductSale_Year
ORDER BY
    ProductName

示例应用程序使用以下代码执行它并使用默认透视列顺序执行客户端透视。

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

如果默认的透视列顺序不是您想要的，您可以指定自定义顺序

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

结论

有两种方法可以执行服务器端动态透视：PIVOT 运算符和 CASE 表达式，两者都需要动态 SQL，您可以选择客户端动态 SQL 和服务器端动态 SQL。但是客户端 透视 可能比服务器端动态 透视 更好。