sql server 2019 行转列动态列

原创

mob649e81593bda 2023-09-17 06:34:05 ©著作权

©著作权归作者所有：来自51CTO博客作者mob649e81593bda的原创作品，请联系作者获取转载授权，否则将追究法律责任

SQL Server 2019 行转列动态列

介绍

在SQL Server数据库中，行转列（Pivot）是一种常见的操作，它可以将行数据转换为列数据，使得数据更易于理解和分析。在SQL Server 2019中，引入了一种新的行转列功能，称为动态列（Dynamic Pivot）。动态列使得我们可以根据实际数据的内容动态地生成列。

本文将介绍如何使用SQL Server 2019的动态列功能进行行转列操作，并提供相应的代码示例。

示例数据

首先，我们来准备一个示例数据表，用于演示动态列的使用。

-- 创建示例数据表
CREATE TABLE Sales (
    Year INT,
    Month INT,
    Product VARCHAR(50),
    Quantity INT
);

-- 插入示例数据
INSERT INTO Sales (Year, Month, Product, Quantity)
VALUES
    (2020, 1, 'Product A', 100),
    (2020, 1, 'Product B', 200),
    (2020, 2, 'Product A', 150),
    (2020, 2, 'Product B', 250);

上述代码创建了一个名为Sales的数据表，并向其中插入了一些示例数据。该数据表包含了年份、月份、产品和数量四个字段。

动态列的使用

动态列使得我们可以根据实际数据的内容动态地生成列。下面是一个使用动态列进行行转列操作的示例：

-- 使用动态列进行行转列操作
DECLARE @cols AS NVARCHAR(MAX);
DECLARE @query AS NVARCHAR(MAX);

-- 获取所有不重复的产品列表
SET @cols = STUFF((
    SELECT DISTINCT ',' + QUOTENAME(Product)
    FROM Sales
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

-- 构建动态查询语句
SET @query = 'SELECT Year, Month, ' + @cols + '
FROM
(
    SELECT Year, Month, Product, Quantity
    FROM Sales
) x
PIVOT
(
    SUM(Quantity)
    FOR Product IN (' + @cols + ')
) p';

-- 执行动态查询语句
EXECUTE(@query);

上述代码中，我们首先使用STUFF()函数获取所有不重复的产品列表，并将其存储在变量@cols中。然后，我们构建了一个动态查询语句@query，该查询语句使用了动态列进行行转列操作。最后，我们使用EXECUTE()函数执行了该动态查询语句。