sql server 行转列动态

原创

mob64ca12e5c0c2 2023-08-12 10:38:02 ©著作权

文章标签 行转列 SQL Apple 文章分类 SQL Server 数据库

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

SQL Server行转列（Pivot）动态

在SQL Server中，行转列（Pivot）是一种常见的操作，它允许我们将行数据转换为列数据。这在某些场景下非常有用，比如将某个表中的多个行转换为多个列，或者将多个列转换为多个行。本文将介绍如何在SQL Server中使用动态行转列（Dynamic Pivot）。

行转列基础

在介绍动态行转列之前，我们首先来了解一下基本的行转列操作。假设有以下表格Orders：

OrderID	Product	Quantity
1	Apple	10
1	Banana	5
2	Apple	8
2	Orange	3

我们希望将上述表格中的Product列转换为多个列，每个列代表一个产品，同时显示对应的数量。使用静态行转列（Static Pivot）可以实现此功能。以下是实现此功能的代码示例：

SELECT OrderID, [Apple], [Banana], [Orange]
FROM
(
  SELECT OrderID, Product, Quantity
  FROM Orders
) AS SourceTable
PIVOT
(
  SUM(Quantity)
  FOR Product IN ([Apple], [Banana], [Orange])
) AS PivotTable;

以上代码中，PIVOT关键字用于执行行转列操作。FOR Product IN ([Apple], [Banana], [Orange])指定了要转换的列，括号中的列名为原始表格中的唯一值。

执行以上代码后，将会得到以下结果：

OrderID	Apple	Banana	Orange
1	10	5	NULL
2	8	NULL	3

可以看到，Product列已经被转换为了多个列。

动态行转列

静态行转列适用于已知列的情况，但在实际应用中，我们经常遇到需要根据数据动态生成列的情况。这时，我们就需要使用动态行转列。

动态行转列的基本原理是通过动态SQL语句来生成转换后的列。以下是一个实现动态行转列的示例代码：

DECLARE @columns NVARCHAR(MAX), @query NVARCHAR(MAX);

SELECT @columns = STUFF((SELECT DISTINCT ',' + QUOTENAME(Product) 
                         FROM Orders
                         FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

SET @query = 'SELECT OrderID, ' + @columns + '
              FROM
              (
                SELECT OrderID, Product, Quantity
                FROM Orders
              ) AS SourceTable
              PIVOT
              (
                SUM(Quantity)
                FOR Product IN (' + @columns + ')
              ) AS PivotTable;';

EXECUTE(@query);

以上代码中，我们首先使用STUFF函数和FOR XML语句来生成列名，然后将生成的列名拼接到动态SQL语句中。

执行以上代码后，将会得到与静态行转列相同的结果。