mssql数据行转列的技巧

1.概述

在数据分析和建模的过程中，经常需要将数据行转换为列。在Microsoft SQL Server（以下简称SQL Server）中，可以使用多种方法实现数据行向列的转换。本文将介绍最常用的两种方法：使用PIVOT和使用UNPIVOT。这两种方法在SQL Server中的实现非常方便，可以在不使用转换工具的情况下实现数据行向列的转换。

2.使用PIVOT进行行转列

2.1 什么是PIVOT？

在SQL Server中，PIVOT是一种将数据从行转换为列的技术。它将从列选择行中的值，然后将这些值作为新的列插入到结果集中。PIVOT可以将简单的SELECT查询转换为具有行列转置功能的查询。

2.2 PIVOT实例

下面是一个使用PIVOT进行行转列的示例。假设我们有一个包含销售数据的表，如下所示：

CREATE TABLE SalesData (
    SalesDate DATE,
    Product VARCHAR(50),
    Amount DECIMAL(18,2)
);
INSERT INTO SalesData VALUES ('2022-01-01', 'ProductA', 100.00);
INSERT INTO SalesData VALUES ('2022-01-01', 'ProductB', 150.00);
INSERT INTO SalesData VALUES ('2022-01-02', 'ProductA', 200.00);
INSERT INTO SalesData VALUES ('2022-01-02', 'ProductB', 250.00);

我们想要将SalesData表中的数据转换为行列格式，以便我们可以更容易地查看每个产品的销售金额和日期。我们可以使用以下查询：

SELECT *
FROM Salesdata
PIVOT
(
  SUM(Amount)
  FOR Product IN ([ProductA], [ProductB])
) as PivotTable

上面的查询将Amount字段聚合为SUM，然后将其放入ProductA和ProductB的新列中。

3.使用UNPIVOT进行列转行

3.1 什么是UNPIVOT？

UNPIVOT是将数据从列到行进行转换的技术。它将列中的值转换为行，然后将这些新的行插入到结果集中。使用UNPIVOT，我们可以将具有行列互换您数据转换为一个标准化格式。

3.2 UNPIVOT实例

接下来，让我们通过以下示例演示UNPIVOT的使用。假设我们有一个包含以下数据的表：

CREATE TABLE MonthlySales (
    Year INT,
    Month1 DECIMAL(18,2),
    Month2 DECIMAL(18,2),
    Month3 DECIMAL(18,2),
    Month4 DECIMAL(18,2),
    Month5 DECIMAL(18,2),
    Month6 DECIMAL(18,2),
    Month7 DECIMAL(18,2),
    Month8 DECIMAL(18,2),
    Month9 DECIMAL(18,2),
    Month10 DECIMAL(18,2),
    Month11 DECIMAL(18,2),
    Month12 DECIMAL(18,2)
);
INSERT INTO MonthlySales VALUES (2022, 100, 200, 300, 400, 500, 600, 700, 800, 900, 1000, 1100, 1200);
INSERT INTO MonthlySales VALUES (2023, 150, 250, 350, 450, 550, 650, 750, 850, 950, 1050, 1150, 1250);

上面的表表示每年的12个月的销售数据。为了使查询更容易，我们可以使用UNPIVOT将此数据转换为标准化的格式，其中包含三个列：Year、Month和SalesAmount。

SELECT Year, SalesMonth, SalesAmount
FROM MonthlySales
UNPIVOT
(
  SalesAmount FOR SalesMonth IN (Month1, Month2, Month3, Month4, Month5, Month6, Month7, Month8, Month9, Month10, Month11, Month12)
) AS UnpivotTable;

上面的查询将每个月的销售数据转换为一行，并将其放入SalesAmount列中。Month和Year列显示每个值的原始位置。

4.结论

在本文中，我们介绍了使用PIVOT和UNPIVOT在SQL Server中实现数据行向列转换的方法。这些方法非常方便，可以在不使用转换工具的情况下快速实现行列转置功能。当您需要将数据转换为行列格式时，这些技术可以帮助您更容易地进行数据分析和建模。