SQL Server中的动态数据透视表

In this article, I am going to explain how we can create a dynamic pivot table in SQL Server. Pivot tables are a piece of summarized information that is generated from a large underlying dataset. It is generally used to report on specific dimensions from the vast datasets. Essentially, the user can convert rows into columns. This gives the users the ability to transpose columns from a SQL Server table easily and create reports as per the requirements.

在本文中，我将解释如何在SQL Server中创建动态数据透视表。 数据透视表是从大型基础数据集中生成的一部分汇总信息。 它通常用于报告海量数据集中的特定维度。 本质上，用户可以将行转换为列。 这使用户能够轻松地转置SQL Server表中的列并根据要求创建报告。

Some pivot tables are also created to help in data analysis, mainly for slicing and dicing with the data and generate analytical queries after all. If you see the figure below, you’ll have some idea how a pivot table is created from a table.

还创建了一些数据透视表来帮助数据分析，主要用于对数据进行切片和切块，并最终生成分析查询。 如果您看到下图，您将对如何从表中创建数据透视表有所了解。

If you see the figure above, you can see that there are two tables. The table on the left is the actual table that contains the original records. The table on the right is a pivot table that is generated by converting the rows from the original table into columns. Basically, a pivot table will contain three specific areas, mainly – rows, columns, and values. In the above illustration, the rows are taken from the Student column, the columns are taken from the Subject, and the values are created by aggregating the Marks column.

如果看到上图，则可以看到有两个表。 左侧的表是包含原始记录的实际表。 右侧的表是通过将原始表中的行转换为列而生成的数据透视表。 基本上，数据透视表将包含三个特定区域，主要是–行，列和值。 在上图中，行是从“ 学生 ”列中获取的，列是从“ 主题”中获取的，而值是通过汇总“ 标记”列而创建的。

创建样本数据 (Creating a sample data)

Now that we have some idea about how a pivot table works let us go ahead and try our hands-on. You can execute the script below to create sample data, and we will try to implement the above illustration here.

现在，我们对数据透视表的工作方式有了一些了解，让我们继续尝试一下。 您可以执行下面的脚本来创建示例数据，我们将尝试在此处实现以上插图。

CREATE TABLE Grades(
  [Student] VARCHAR(50),
  [Subject] VARCHAR(50),
  [Marks]   INT
)
GO
 
INSERT INTO Grades VALUES 
('Jacob','Mathematics',100),
('Jacob','Science',95),
('Jacob','Geography',90),
('Amilee','Mathematics',90),
('Amilee','Science',90),
('Amilee','Geography',100)
GO

Let us try to select the data from the table that we just created as below.

让我们尝试从刚才创建的表中选择数据，如下所示。

应用PIVOT运算符 (Applying the PIVOT Operator)

Now that we have our data ready, we can go ahead and create the pivot table in SQL Server. Considering the same illustration as above, we will keep the Student column as rows and take the Subject for the columns. Also, another important point to note here is that while writing the query for the pivot table in SQL Server, we need to provide a distinct list of column values that we would like to visualize in the pivot table. For this script, we can see that we have three distinct subjects available in the original dataset, so we must provide these three in the list while creating the pivot table.

现在我们已经准备好数据，接下来可以在SQL Server中创建数据透视表。 考虑与上述相同的图示，我们将“ 学生”列保留为行，并以“ 主题 ”作为列。 另外，这里要注意的另一个重要点是，在SQL Server中编写对数据透视表的查询时，我们需要提供要在数据透视表中可视化的列值的独特列表。 对于此脚本，我们可以看到原始数据集中有三个不同的主题，因此在创建数据透视表时必须在列表中提供这三个主题。

SELECT * FROM (
  SELECT
    [Student],
    [Subject],
    [Marks]
  FROM Grades
) StudentResults
PIVOT (
  SUM([Marks])
  FOR [Subject]
  IN (
    [Mathematics],
    [Science],
    [Geography]
  )
) AS PivotTable

As you can see in the figure above, the pivot table has been created and we have converted the rows for Subjects into distinct columns.

如上图所示，数据透视表已创建，并且我们已将“ 主题”的行转换为不同的列。

Now let us try to break the above script and understand how it works. If you see the script, clearly, we can divide it into two separate sections – the first part in which we select data from the original table as it is and in the second part, we define how the pivot table should be created. In the script, we also mention some specific keywords like SUM, FOR and IN, which are meant for use by the PIVOT operator only. Let’s quickly talk about these keywords.

现在让我们尝试破坏上面的脚本并了解其工作原理。 如果您清楚地看到了脚本，我们可以将其分为两个部分–第一部分，我们从原表中按原样选择数据，第二部分，定义应如何创建数据透视表。 在脚本中，我们还提到了一些特定的关键字，例如SUM ， FOR和IN，仅供PIVOT运算符使用。 让我们快速讨论一下这些关键字。

SUM运算符 (The SUM operator )

In the script, I have used the SUM operator, which will essentially aggregate the values from the Marks column so that it can be used in the pivot table. It is mandatory for the pivot operator to use an aggregated column that it can display for the values sections.

在脚本中，我使用了SUM运算符，该运算符实质上将汇总Marks列中的值，以便可以在数据透视表中使用它。 数据透视表运算符必须使用可在值部分显示的聚合列。

FOR关键字 (The FOR keyword)

The FOR keyword is a special keyword used for the pivot table in SQL Server scripts. This operator tells the pivot operator on which column do we need to apply the pivot function. Basically, the column which is to be converted from rows into columns.

FOR关键字是用于SQL Server脚本中的数据透视表的特殊关键字。 该运算符告诉枢轴运算符，我们需要在哪一列上应用枢轴函数。 基本上是要从行转换为列的列。

IN关键字 (The IN keyword)

The IN keyword, as already explained above, lists all the distinct values from the pivot column that we want to add to the pivot table column list. For this example, since we have only three distinct values for the Subject column, we provide all the three in the list for the IN keyword.

如上所述，IN关键字列出了我们要添加到数据透视表列列表中的数据透视表列中所有不同的值。 在此示例中，由于主题列只有三个不同的值，因此我们在列表中为IN关键字提供了所有三个。

The only limitation in this process is that we need to provide hardcoded values for the columns that we need to select from the pivot table. For instance, if a new subject value is inserted into the table, the pivot table won’t be able to display the new value as a column because it is not defined in the list for the IN operator. Let us go ahead and insert a few records into the table for a different subject – “History“.

此过程中的唯一限制是，我们需要为需要从数据透视表中选择的列提供硬编码值。 例如，如果将新的主题值插入表中，则数据透视表将无法将新值显示为列，因为该值未在IN运算符的列表中定义。 让我们继续，在表中为其他主题（“ 历史 ”）插入一些记录。

INSERT INTO Grades VALUES 
('Jacob','History',80),
('Amilee','History',90)
GO

Let us execute the query for displaying the pivot table as we did previously.

让我们像以前一样执行查询以显示数据透视表。

As you can see, the new subject that we just inserted into the table is not available in the PIVOT table. This is because we did not mention the new column in the IN list of the PIVOT operator. This is one of the limitations of the PIVOT table in SQL. Each time we want to include a new column in the PIVOT, we would need to go and modify the underlying code.

如您所见，我们刚刚插入表中的新主题在PIVOT表中不可用。 这是因为我们没有在PIVOT运算符的IN列表中提及新列。 这是SQL中PIVOT表的限制之一。 每次我们想要在PIVOT中包括一个新列时，我们都需要去修改基础代码。

Another scenario would be like if the requirements change and now, we need to pivot students instead of the subjects, even in such a case, we would need to modify the entire query. In order to avoid this, we can create something dynamic in which we can configure the columns on which we would need the PIVOT table. Let’s go ahead and understand how to make a dynamic stored procedure that will return a PIVOT table in SQL.

另一种情况是，如果需求发生变化，现在我们需要以学生而不是科目为中心，即使在这种情况下，我们也需要修改整个查询。 为了避免这种情况，我们可以创建动态的东西，在其中可以配置需要PIVOT表的列。 让我们继续了解如何制作动态存储过程，该存储过程将在SQL中返回PIVOT表。

为PIVOT表建立动态存储过程 (Building a Dynamic Stored Procedure for PIVOT Tables)

Let’s encapsulate the entire PIVOT script in a stored procedure. This stored procedure will have the configurable options in which we should be able to customize our requirements just by altering some parameterized values. The script for the dynamic PIVOT table in SQL is below.

让我们将整个PIVOT脚本封装在一个存储过程中。 该存储过程将具有可配置的选项，在这些选项中，我们只需更改一些参数化值就可以自定义需求。 SQL中动态PIVOT表的脚本如下。

CREATE PROCEDURE dbo.DynamicPivotTableInSql
  @ColumnToPivot  NVARCHAR(255),
  @ListToPivot    NVARCHAR(255)
AS
BEGIN
 
  DECLARE @SqlStatement NVARCHAR(MAX)
  SET @SqlStatement = N'
    SELECT * FROM (
      SELECT
        [Student],
        [Subject],
        [Marks]
      FROM Grades
    ) StudentResults
    PIVOT (
      SUM([Marks])
      FOR ['+@ColumnToPivot+']
      IN (
        '+@ListToPivot+'
      )
    ) AS PivotTable
  ';
 
  EXEC(@SqlStatement)
 
END

As you can see in the script above, I have two parameterized variables. The details of these two parameters are as follows.

如您在上面的脚本中看到的，我有两个参数化的变量。 这两个参数的详细信息如下。

• @ColumnToPivot – This parameter accepts the name of the column in the base table on which the pivot table is going to be applied. For the current scenario, it will be the “@ColumnToPivot –此参数接受将在其上应用数据透视表的基础表中的列的名称。 对于当前方案，它将是“ Subject” column because we would like to pivot the base table and display all the subjects in the columns Subject ”列，因为我们想旋转基表并在列中显示所有主题
• @ListToPivot – This parameter accepts the list of values that we want to visualize as a column in the pivot table in SQL @ListToPivot –此参数接受我们要可视化为SQL中数据透视表中的列的值的列表

执行动态存储过程 (Executing the Dynamic Stored Procedure)

Now that our dynamic stored procedure is ready let us go ahead and execute it. Let us replicate the first scenario where we visualized all the three subjects – Mathematics, Science and Geography in the pivot table in SQL. Execute the script as below.

现在我们的动态存储过程已经准备就绪，让我们继续执行它。 让我们复制第一个场景，在该场景中，我们在SQL的数据透视表中可视化了所有三个主题-数学，科学和地理。 执行如下脚本。

EXEC dbo.DynamicPivotTableInSql
  N'Subject'
  ,N'[Mathematics],[Science],[Geography]'

As you can see, we have now provided the name of the column “Subject” as the first parameter and the list of pivot columns as the second column.

如您所见，我们现在提供了“ Subject ”列的名称作为第一个参数，并提供了透视列的列表作为第二列。

Suppose, now we would also like to include the marks for the column “History” in this pivot table, the only thing that you should do is to add the name of the column in the second parameter and execute the stored procedure.

假设，现在我们还希望在此数据透视表中包括“ 历史记录 ”列的标记，您唯一要做的就是在第二个参数中添加列的名称并执行存储过程。

EXEC dbo.DynamicPivotTableInSql
  N'Subject'
  ,N'[Mathematics],[Science],[Geography],[History]'

As easy as that, you can add as many columns you’d like to add to the list and the pivot table will be displayed accordingly.

如此简单，您可以将想要添加的列添加到列表中，数据透视表将相应显示。

Let us now consider another scenario where you need to display the name of the students on the columns and the subjects on the rows—just a vice versa scenario of what we have been doing all along. The solution is also simple, as you might have expected. We will just modify the values in both the parameters such that the first parameter indicates the column “Student” and the second parameter will contain the list of students that you want along with the columns. The stored procedure is as follows.

现在让我们考虑另一种情况，您需要在列上显示学生的姓名，在行上显示学科的名称，反之亦然。 如您所料，解决方案也很简单。 我们将只修改两个参数中的值，以使第一个参数指示“ Student ”列，第二个参数将包含您想要的学生列表。 存储过程如下。

EXEC dbo.DynamicPivotTableInSql
  N'Student'
  ,N'[Amilee],[Jacob]'

As you can see in the image above, the pivot table in SQL is dynamically modified without having to modify the underlying code behind.

如上图所示，SQL中的数据透视表是动态修改的，而无需修改后面的基础代码。

结论 (Conclusion)

In this article, I have explained what a pivot table in SQL is and how to create one. I have also demonstrated a simple scenario in which you can implement pivot tables. Finally, I have also shown how to parameterize the pivot table script such that the values table structure can be easily altered without having to modify the underlying code.

在本文中，我解释了什么是SQL中的数据透视表以及如何创建数据透视表。 我还演示了一个简单的场景，您可以在其中实现数据透视表。 最后，我还展示了如何对数据透视表脚本进行参数化，以便可以轻松更改值表结构，而无需修改基础代码。

翻译自: https://www.sqlshack.com/dynamic-pivot-tables-in-sql-server/