使用SQL Server实现行转列

1. 什么是行转列

在实际数据库中，我们有时会遇到需要将行数据转换为列数据的情况。行转列是一种数据转换技术，通常用于将一行数据转成一列，这样在数据处理和分析上更为方便。SQL Server中可以使用多种方法实现行转列，本文主要介绍利用pivot和unpivot函数实现行转列的方法。

2. pivot函数实现行转列

2.1 pivot函数概述

pivot是SQL Server中的一种数据转换函数，它可以将一张表中的一列或多列数据旋转到行数据，同时根据汇总条件进行聚合。利用pivot函数可以将行数据转换为列数据，方便数据处理和分析。

2.2 pivot函数使用方法

使用pivot函数实现行转列需要满足以下条件：

源表必须拥有1个或多个聚合列和1个或多个分类列。

聚合列必须是数值类型。

在满足以上条件的前提下，可以使用pivot函数实现行转列。pivot函数语法如下：

SELECT <non-pivoted column>, 

    [first pivoted column] AS <column name>, 
    [second pivoted column] AS <column name>,
    ...
FROM 
    <table name>
PIVOT
(
    <aggregation function>(<value column>)
FOR 
    <pivot column> IN (<column list>)
) AS <alias>;

其中，<non-pivoted column>表示不需要转换的列名，<column name>表示转换后的列名，<table name>表示源表名，<aggregation function>表示聚合函数，<value column>表示聚合列名，<pivot column>表示分类列名，<column list>表示分类列值列表，<alias>表示转换后的表名。

2.3 使用pivot函数实现行转列示例

下面通过一个简单的示例来演示如何使用pivot函数实现行转列：

-- 创建测试表

CREATE TABLE Score
(
    StudentID INT,
    Subject VARCHAR(20),
    Score FLOAT
);
-- 插入测试数据
INSERT INTO Score(StudentID, Subject, Score)
VALUES
(1, '语文', 80),
(1, '数学', 90),
(1, '英语', 85),
(2, '语文', 75),
(2, '数学', 95),
(2, '英语', 90),
(3, '语文', 85),
(3, '数学', 85),
(3, '英语', 80);
-- 使用pivot函数实现行转列
SELECT *
FROM 
(
    SELECT StudentID, Subject, Score
    FROM Score
) AS s
PIVOT
(
    AVG(Score)
    FOR Subject IN ([语文], [数学], [英语])
) AS p;

在上面的示例中，我们首先创建一个名为Score的测试表，包含3个字段：学生ID(StudentID)、科目(Subject)、成绩(Score)。然后向测试表中插入了9条测试数据。最后使用pivot函数将Score表中的数据行(即每个学生)转换为Score表中的列(即每个科目)，并取每个科目的平均成绩。结果如下：

3. unpivot函数实现列转行

3.1 unpivot函数概述

与pivot函数不同，unpivot函数是将列数据转成行数据的数据转换技术。使用unpivot函数可以将垂直存储的数据转换为水平存储的数据，方便数据处理和分析。unpivot函数的作用是将一张表的列数据旋转到行数据，并将列名称和列值分别存储到两个字段中。

3.2 unpivot函数使用方法

使用unpivot函数实现列转行需要满足以下条件：

源表必须至少存在两列，其中一列为列名，另一列为列值。

不同的列名必须具有相同的数据类型。

在满足以上条件的前提下，可以使用unpivot函数实现列转行。unpivot函数语法如下：

SELECT <id column>, 

    <attribute column>, 
    <value column> 
FROM <table name> 
UNPIVOT 
( 
    <value column> FOR <attribute column> IN 
    (<column name>,<column name>,...) 
) AS <alias>;

其中，<id column>表示表中的唯一标识，<attribute column>表示列名称字段，<value column>表示列值字段，<table name>表示源表名，<column name>表示转换的列名称列表，<alias>表示转换后的表名。

3.3 使用unpivot函数实现列转行示例

下面通过一个简单的示例来演示如何使用unpivot函数实现列转行：

-- 创建测试表

CREATE TABLE Employee
(
    EmpID INT,
    FirstName VARCHAR(20),
    LastName VARCHAR(20),
    Salary DECIMAL(10,2),
    Bonus DECIMAL(10,2)
);
-- 插入测试数据
INSERT INTO Employee(EmpID, FirstName, LastName, Salary, Bonus)
VALUES(1, 'John', 'Doe', 50000, 5000),
      (2, 'Jane', 'Doe', 60000, 6000),
      (3, 'Bob', 'Smith', 70000, 7000);
-- 使用unpivot函数实现列转行
SELECT EmpID, Attribute, Value
FROM Employee
UNPIVOT
(
    Value FOR Attribute in (Salary, Bonus)
) AS u;

在上面的示例中，我们首先创建一个名为Employee的测试表，包含5个字段：员工ID(EmpID)、名(FirstName)、姓(LastName)、工资(Salary)、奖金(Bonus)。然后向测试表中插入了3条测试数据。最后使用unpivot函数将Employee表的数据列(即Salary和Bonus)转换为Employee表中的数据行，并分别存储到Attribute和Value字段中。结果如下：

4. 总结

在SQL Server中，利用pivot和unpivot函数可以方便地实现行转列和列转行的数据转换技术。pivot函数可以将源表中的行数据转换为列数据，并根据聚合条件进行计算；unpivot函数则可以将源表中的列数据转换为行数据，方便数据分析和处理。在实际开发过程中，根据实际需求选择合适的数据转换方法能够大大提升数据处理和分析的效率。