SQL Server 转置|极客教程

SQL Server 转置

在SQL Server数据库中，转置操作是将行数据转换为列数据的过程。常见的情形是将表中的行数据按照某种条件进行汇总，然后将这些汇总结果作为新的列数据展示。在本文中，我们将详细讨论SQL Server中如何实现转置操作，包括使用PIVOT和动态SQL两种不同的实现方式。

PIVOT操作

PIVOT是SQL Server提供的一种行列转换的功能，通常用于将行数据转换为列数据。其基本语法如下：

SELECT <非转置字段>,
       [转置字段1],
       [转置字段2],
       ...
FROM <源表>
PIVOT
(
    <聚合函数>(<值字段>)
    FOR <Pivot列>
    IN ([转置字段1], [转置字段2], ...)
) AS <新表别名>

其中，<非转置字段>为需要保留的原始字段，用于标识每一行数据；<值字段>为需要进行转置的字段；<Pivot列>为作为转置依据的列；[转置字段1], [转置字段2]为转置后的列数据列名。

示例

假设有如下的原始表SalesData：

Region	Product	SalesAmount
East	Apple	1000
East	Banana	2000
West	Apple	1500
West	Banana	2500

我们希望按照不同的产品转置销售金额，并按照地区作为标识列。可以使用以下SQL语句进行转置操作：

SELECT Region, [Apple], [Banana]
FROM
(
    SELECT Region, Product, SalesAmount
    FROM SalesData
) AS SourceTable
PIVOT
(
    SUM(SalesAmount)
    FOR Product IN ([Apple], [Banana])
) AS PivotTable

执行以上SQL语句后，将得到如下的转置结果：

Region	Apple	Banana
East	1000	2000
West	1500	2500

动态SQL实现

除了PIVOT操作外，我们还可以通过动态SQL实现转置操作。动态SQL的优势在于可以根据实际情况动态生成SQL语句，适用于转置字段不固定的情况。其基本思路是通过动态生成SQL语句，然后通过动态执行SQL语句来实现转置操作。

示例

假设我们需要将销售数据按照产品进行转置，并且产品种类不固定。我们可以按照以下步骤进行实现：

查询出所有产品种类；
构建动态SQL语句；
执行动态SQL语句进行转置操作。

以下是具体的实现代码：

DECLARE @ProductList NVARCHAR(MAX)
DECLARE @DynamicSQL NVARCHAR(MAX)

-- Step 1: 查询出所有产品种类
SELECT @ProductList = COALESCE(@ProductList + ', ', '') + QUOTENAME(Product)
FROM (SELECT DISTINCT Product FROM SalesData) AS ProductList

-- Step 2: 构建动态SQL语句
SET @DynamicSQL = N'
SELECT Region, ' + @ProductList + '
FROM
(
    SELECT Region, Product, SalesAmount
    FROM SalesData
) AS SourceTable
PIVOT
(
    SUM(SalesAmount)
    FOR Product IN (' + @ProductList + ')
) AS PivotTable
'

-- Step 3: 执行动态SQL语句
EXEC sp_executesql @DynamicSQL

通过以上动态SQL的实现，我们可以适应不固定的转置字段进行转置操作。