How to pivot rows into columns in SQL Server?

2 years ago
Ava Mitchell
2 minutes

In SQL Server, you can use the PIVOT operation to transform rows into columns. PIVOT operation allows you to use unique values as column headers and populate the new columns with values from the rows.

Here are some examples of using the PIVOT operation:

  1. Basic PIVOT operation:
SELECT *
FROM (
   SELECT column1, column2, column3
   FROM table_name
) AS SourceTable
PIVOT (
   MAX(column3) -- 选择一个聚合函数，例如SUM、COUNT、MAX等
   FOR column1 -- 列标题所在的列
   IN (value1, value2, value3) -- 列标题的唯一值列表
) AS PivotTable;
  1. Performing a PIVOT operation with dynamic column headers.
DECLARE @columnList AS NVARCHAR(MAX);
DECLARE @pivotQuery AS NVARCHAR(MAX);

SELECT @columnList = CONCAT(@columnList, ', ', QUOTENAME(column1))
FROM (
   SELECT DISTINCT column1
   FROM table_name
) AS ColumnListTable;

SET @pivotQuery = CONCAT('
   SELECT *
   FROM (
      SELECT column1, column2, column3
      FROM table_name
   ) AS SourceTable
   PIVOT (
      MAX(column3)
      FOR column1
      IN (', @columnList, ')
   ) AS PivotTable;
');

EXECUTE sp_executesql @pivotQuery;

In the example above, table_name refers to the name of the original table being converted, column1 is the name of the column containing the column headers, and column2 and column3 are the names of the columns containing the values to be converted. value1, value2, and value3 are unique value lists for the column headers.

It is important to note that the PIVOT operation is specific to SQL Server and may not be applicable to all database management systems. If you are using a different database, you may need to utilize a different method to achieve the transformation from rows to columns.

bannerAds