SQL Server Pivot 行转列

简介

在SQL Server中，Pivot是一种将行转换为列的操作，它可以将一列中的唯一值作为列名，并将对应的值填充到新列中。这对于定制报表和数据分析非常有用。在本文中，我们将介绍如何使用SQL Server Pivot实现行转列的功能，并给出详细的步骤和示例代码。

流程图

下面是整个行转列的流程图，用于说明整个过程的步骤和顺序。

graph TD
A[选择需要转换的表和字段] --> B[编写转换的查询语句]
B --> C[执行查询并将结果保存到临时表]
C --> D[使用动态SQL构建Pivot查询]
D --> E[执行Pivot查询并获取转换后的结果]

步骤说明

  1. 选择需要转换的表和字段：首先，你需要确定需要进行行转列操作的表和字段。通常，你将选择一个具有多个唯一值的字段作为列名，并将另一个字段的值填充到新的列中。
  2. 编写转换的查询语句：基于所选表和字段，你需要编写一条SQL查询语句来提取需要转换的数据。你可以使用SELECT语句来选择需要的字段，并使用FROM子句指定表。
  3. 执行查询并将结果保存到临时表：将查询结果保存到一个临时表中，以便后续使用。你可以使用SELECT INTO语句将查询结果插入到一个新的表中。
  4. 使用动态SQL构建Pivot查询：使用动态SQL来构建Pivot查询语句。你可以使用SELECT DISTINCT语句来获取需要转换的唯一值，并使用FOR XML PATH语句来构建动态SQL查询。
  5. 执行Pivot查询并获取转换后的结果：执行动态SQL查询并获取转换后的结果。你可以使用EXEC语句来执行动态SQL查询，并将结果保存到一个结果表中。

下面是每个步骤需要做的事情以及对应的代码和注释：

  1. 选择需要转换的表和字段

    • 表名：选择需要进行行转列操作的表，例如myTable。
    • 列名字段：选择作为列名的字段，例如columnName。
    • 值字段：选择需要填充到新列中的字段，例如valueField。

  2. 编写转换的查询语句

-- 选择需要的字段
SELECT columnName, valueField
FROM myTable
  1. 执行查询并将结果保存到临时表
-- 创建临时表
SELECT columnName, valueField
INTO #tempTable
FROM myTable
  1. 使用动态SQL构建Pivot查询
-- 构建动态SQL查询
DECLARE @pivotColumns NVARCHAR(MAX)
SET @pivotColumns = STUFF((SELECT DISTINCT ', ' + QUOTENAME(columnName) 
                           FROM myTable
                           FOR XML PATH('')), 1, 2, '')

DECLARE @pivotQuery NVARCHAR(MAX)
SET @pivotQuery = 'SELECT *
                   FROM #tempTable
                   PIVOT (MAX(valueField) FOR columnName IN (' + @pivotColumns + ')) AS pvt'

-- 打印动态SQL查询
PRINT @pivotQuery
  1. 执行Pivot查询并获取转换后的结果
-- 执行动态SQL查询并获取结果
EXEC(@pivotQuery)

示例

以下是一个示例，演示如何使用SQL Server Pivot实现行转列的功能。

示例表结构

假设我们有一个表sales，包含以下字段：year（年份），month（月份），product（产品名称），amount（销售额）。

示例数据

year month product amount
2020 1 A 100
2020 1 B 200
2020 2 A 150
2020 2 B 250
2021 1 A 120
2021 1 B