SQLServer行列转换方法|极客教程

SQLServer行列转换方法

在实际的数据处理过程中，有时候需要对数据进行行列转换，将表格中的某些行转换为列，或者将列转换为行，以满足不同的分析需求。在SQLServer中，我们可以使用一些方法来实现行列转换操作，本文将详细讨论这些方法。

使用PIVOT进行行列转换

PIVOT是SQLServer中用于行列转换的关键字，可以将行转换为列。其基本语法如下：

SELECT <non-pivoted column>, [first pivoted column],
    [second pivoted column],
    ...
FROM
    (<SELECT query that produces the data>) AS <alias>
PIVOT
(
    <aggregation function>(<column being aggregated>)
    FOR
    [<column that contains the values that will become column headers>]
    IN ( [first pivoted column], [second pivoted column],
    ...,[last pivoted column])
) AS <alias>;

在上面的语法中，<aggregation function>可以是SUM、COUNT、MAX、MIN等聚合函数，<column being aggregated>为被聚合的列，<column that contains the values that will become column headers>指定了需要转换为列头的列名。

下面我们通过一个示例来说明PIVOT的用法。

假设我们有如下的Sales表：

SalesID	Product	Amount	Year
1	A	100	2019
2	B	200	2019
3	A	150	2020
4	B	250	2020

现在我们想要将每个产品在不同年份的销售额转换为列，可以使用如下SQL语句：

SELECT Product, [2019], [2020]
FROM
(SELECT Product, Amount, Year FROM Sales) AS Src
PIVOT
(
SUM(Amount)
FOR Year IN ([2019], [2020])
) AS PivotTable;

运行以上代码，将得到以下结果：

Product	2019	2020
A	100	150
B	200	250

在上面的示例中，我们使用了PIVOT将原本按年份存储的数据转换为了按产品存储的数据，并计算了每个产品在不同年份的销售额总和。

使用CROSS APPLY进行行列转换

除了PIVOT之外，SQLServer还可以使用CROSS APPLY进行行列转换。CROSS APPLY是一种表值运算符，它可以将一个表的结果应用于另一个表。通过CROSS APPLY，我们可以执行一些行列转换的操作。

以下是CROSS APPLY的基本语法：

SELECT <non-pivoted column>, [pivoted column]
FROM
    <table>
CROSS APPLY
    <table-valued function>

在上面的语法中，<table>是表，<table-valued function>是返回表格的函数。

下面我们通过一个示例来说明CROSS APPLY的用法。

假设我们有如下的Employee表：

EmployeeID	Skill
1	A
2	B,C
3	A,B,C
4	C,D

现在我们想要将每个员工的技能拆分为不同的行，可以使用如下SQL语句：

SELECT EmployeeID, Skill
FROM Employee
CROSS APPLY STRING_SPLIT(Skill, ',');

运行以上代码，将得到以下结果：

EmployeeID	Skill
1	A
2	B
2	C
3	A
3	B
3	C
4	C
4	D

在上面的示例中，我们使用CROSS APPLY和STRING_SPLIT函数将每个员工的技能按逗号拆分为不同的行，实现了行列转换的操作。

使用UNPIVOT进行列行转换

与PIVOT相对应的是UNPIVOT，它可以将列转换为行，将多列数据转换为多行数据。UNPIVOT的基本语法如下：

SELECT <id columns>, <value>
FROM
    <table>
UNPIVOT
    (<value> FOR <column> IN (<column1>, <column2>, ...)) AS <alias>

在上面的语法中，<id columns>指原表中需要保留的列，<value>为需要转换的列值，<column1>, <column2>, ...为需要转换的列名。

下面我们通过一个示例来说明UNPIVOT的用法。

假设我们有如下的Sales表：

Year	A	B	C
2019	100	200	300
2020	150	250	350

现在我们想要将每年的销售额转换为多行数据，可以使用如下SQL语句：

SELECT Year, Product, Amount
FROM
(SELECT Year, A, B, C FROM Sales) AS Src
UNPIVOT
(
Amount FOR Product IN (A, B, C)
) AS UnpivotTable;

运行以上代码，将得到以下结果：

Year	Product	Amount
2019	A	100
2019	B	200
2019	C	300
2020	A	150
2020	B	250
2020	C	350

在上面的示例中，我们使用了UNPIVOT将原本按列存储的数据转换为了按行存储的数据，并将每年的销售额拆分为了不同的产品行。

总结

本文介绍了在SQLServer中进行行列转换的方法，包括使用PIVOT实现行列转换、使用CROSS APPLY进行行列转换以及使用UNPIVOT进行列行转换。这些方法在实际数据处理中非常实用，可以根据不同的需求选择适合的方法来实现行列转换操作。

SQLServer行列转换方法