SQL Server Pivot函数查询的动态结果如何插入一个新的表中|极客教程

SQL Server Pivot函数查询的动态结果如何插入一个新的表中

在SQL Server数据库中，使用Pivot函数可以将行数据转换为列数据，这在处理交叉表数据时非常有用。然而，有时候我们需要将Pivot查询的动态结果插入到一个新的表中，以便进一步分析或使用这些数据。本文将详细介绍如何实现这个过程。

1. Pivot函数简介

Pivot函数是SQL Server中用于转置行数据为列数据的一种功能。通过Pivot函数，可以将某个列的值作为新的列头，原来的值作为新列的值。这在处理需要将行数据进行转置的情况下非常有用。

2. 动态Pivot查询结果示例

首先，我们来看一个简单的动态Pivot查询的示例。假设我们有一个Sales表，包含产品销售的信息，数据如下：

ProductID	ProductName	SaleDate	SaleAmount
1	Product A	2022-01-01	100
2	Product B	2022-01-02	200
1	Product A	2022-01-03	150
2	Product B	2022-01-03	250

我们希望按照产品名称将销售金额在不同日期的统计值进行Pivot查询。下面是一个动态Pivot查询的示例：

DECLARE @cols AS NVARCHAR(MAX),
    @query AS NVARCHAR(MAX);

SELECT @cols = STUFF((SELECT distinct ',' + QUOTENAME(SaleDate) 
            FROM Sales
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

SET @query = 'SELECT ProductName, ' + @cols + ' from 
            (select ProductName, SaleDate, SaleAmount
            from Sales) x
            pivot 
            (
                sum(SaleAmount)
                for SaleDate in (' + @cols + ')
            ) p '

EXEC(@query);

上面的查询会动态生成列头，将产品名称作为行头，不同日期的销售金额作为列头，并显示相应的销售金额数据。

3. 将动态Pivot查询结果插入新表的步骤

接下来，我们将介绍如何将这个动态Pivot查询的结果插入到一个新的表中。

首先，我们可以使用INSERT INTO SELECT 语句来实现这个目的。具体步骤如下：

步骤1：创建新表

首先，我们需要创建一个新表来存储动态Pivot查询的结果。可以使用CREATE TABLE 语句来创建一个新表，如下：

CREATE TABLE dbo.SalesPivotResult
(
    ProductName VARCHAR(50),
    [2022-01-01] INT,
    [2022-01-02] INT,
    [2022-01-03] INT
);

步骤2：插入动态Pivot查询结果

接下来，我们可以使用INSERT INTO SELECT 语句将动态Pivot查询的结果插入到新表中。修改上面的Pivot查询脚本，将结果插入到新表中，如下：

DECLARE @cols AS NVARCHAR(MAX),
    @query AS NVARCHAR(MAX);

SELECT @cols = STUFF((SELECT distinct ',' + QUOTENAME(SaleDate) 
            FROM Sales
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

SET @query = 'INSERT INTO dbo.SalesPivotResult
            SELECT ProductName, ' + @cols + ' from 
            (select ProductName, SaleDate, SaleAmount
            from Sales) x
            pivot 
            (
                sum(SaleAmount)
                for SaleDate in (' + @cols + ')
            ) p '

EXEC(@query);

执行上面的脚本，动态Pivot查询的结果将插入到新创建的SalesPivotResult表中。