MSSQL中将列转换为行的技术

1. SQL语句中的PIVOT和UNPIVOT

1.1 PIVOT

PIVOT是一种在SQL中实现列转行的技术。在PIVOT操作中，您可以将一列数据转换为多个列，这些列可能来自同一列中的不同记录，也可能来自多个列。要执行PIVOT操作，您需要使用以下语法：

SELECT [non-pivoted column],
    [first pivoted column] AS column1,
    [second pivoted column] AS column2,
    ...
FROM
    (SELECT [non-pivoted column],
        [pivoted column],
        [value]
    FROM [table]) 
    AS [alias]
PIVOT
(
    [aggregate function]([value])
FOR
[pivoted column] 
    IN ( [first pivoted column], [second pivoted column],
    ...,[last pivoted column])
) AS [alias];

语法中的“[non-pivoted column]”表示您想要在结果集中包括的任何非PIVOT列。 "[first pivoted column]”， “[second pivoted column]”，“[third pivoted column]”等表示您想要转换成结果集中的新列的每个唯一列值。

其中，“ [aggregate function]”可能是SUM，AVG，COUNT，MAX，MIN等聚合函数，用于汇总Pivot值。

1.2 UNPIVOT

除了PIVOT之外，SQL还提供了UNPIVOT技术来执行从列到行的转换。同样，您可以从多个列中将多个列值转换为单个列值或单个列中的多个值。要执行UNPIVOT操作，您需要使用以下语法：

SELECT [unpivoted column], [pivoted column], [value]
FROM
   (SELECT [non-pivoted column], [pivoted column] 
   FROM [table]
   ) AS [alias]
UNPIVOT
   ([value] FOR [pivoted column] IN 
      ([first pivoted column], [second pivoted column], 
      ... [last pivoted column])
) AS [alias];

在UNPIVOT操作中，语法中的“[unpivoted column]”表示输出中的列，即在每个结果行中都包含的列。“[pivoted column]”表示您在使用PIVOT时使用的列，即要进行转换的列。UNPIVOT使用与PIVOT相同的语法来指定要转换的列。

2. 使用PIVOT和UNPIVOT的示例

2.1 示例

让我们看一个简单的示例，我们从以下Sales表中获取数据：

CREATE TABLE Sales
(
   Year INT,
   Category VARCHAR(50),
   SalesAmount MONEY
)
INSERT INTO Sales VALUES(2019,'Electronics',10000)
INSERT INTO Sales VALUES(2019,'Books',9000)
INSERT INTO Sales VALUES(2019,'Clothing',11000)
INSERT INTO Sales VALUES(2020,'Electronics',12000)
INSERT INTO Sales VALUES(2020,'Books',11000)
INSERT INTO Sales VALUES(2020,'Clothing',13000)

要查看不同年份中每个类别的销售额，我们可以使用PIVOT：

SELECT Category, [2019], [2020]
FROM Sales
PIVOT
(
   SUM(SalesAmount)
   FOR Year IN ([2019], [2020])
) AS P

运行上述代码后，您将看到以下输出：

此处未包含任何未PIVOT的列，因此只选择了类别和年度数据。此外，此代码中的“SUM（SalesAmount）”函数将用于将销售额值累加为相同的类别和年份。

2.2 示例

接下来，我们将使用UNPIVOT操作将两个不同的列转换为一列，这意味着将执行从列到行的转换。我们使用上一个示例中的Sales表并执行以下查询：

SELECT *
FROM
(
   SELECT Year, Category, SalesAmount
   FROM Sales
) AS P
UNPIVOT
(
   Sales FOR SalesType IN (SalesAmount)
) AS UnPvt

运行此代码后，您将看到以下输出：

此例中使用的“[Sales FOR SalesType]”子句将所有传递给SELECT语句的列作为非PIVOT列传递。“UNPIVOT”实际上将[A], [B]和[C]列转换为一列，其中每一行表示销售金额及其列名称“SaleType”在线下。

3. 总结

通过以上示例，您可以看到，无论是PIVOT还是UNPIVOT，都是将列转换为行/行转换为列的有用技术，可以在某些情况下对您的查询和报表非常有用。但是，当您在编写SQL查询时使用这些语句时，请确保对代码进行严格的测试和错误检查，并验证结果是否准确。