Microsoft SQL Server
PIVOT / UNPIVOT

Синтаксис

• SELECT <non-pivoted column> ,
  [первый поворотный столбец] AS <column name> ,
  [второй поворотный столбец] AS <column name> ,
  ...
  [последний поворотный столбец] AS <column name>
  ОТ
  ( <SELECT query that produces the data> )
  AS <alias for the source query>
  PIVOT
  (
  <aggregation function> ( <column being aggregated> )
  ЗА
  [ <column that contains the values that will become column headers> ]
  IN ([первый поворотный столбец], [второй поворотный столбец],
  ... [последний поворотный столбец])
  ) AS <alias for the pivot table> <optional ORDER BY clause> ;

замечания

Простая ось - статические столбцы

Используя таблицу продаж товаров из базы данных примеров , давайте вычислим и покажем общее количество, которое мы продали для каждого Продукта.

Это можно легко сделать с помощью группы, но давайте предположим, что мы «вращаем» нашу таблицу результатов так, чтобы для каждого идентификатора продукта был столбец.

SELECT [100], [145]
  FROM (SELECT ItemId , Quantity
          FROM #ItemSalesTable
       ) AS pivotIntermediate
 PIVOT (   SUM(Quantity)
           FOR ItemId IN ([100], [145])
       ) AS pivotTable

Поскольку наши «новые» столбцы - это числа (в исходной таблице), нам нужно заключить в квадратные скобки []

Это даст нам

Простой PIVOT & UNPIVOT (T-SQL)

Ниже приведен простой пример, показывающий среднюю цену каждого товара за будний день.

Во-первых, предположим, что у нас есть таблица, которая хранит ежедневные отчеты о ценах всех предметов.

CREATE TABLE tbl_stock(item NVARCHAR(10), weekday NVARCHAR(10), price INT);

INSERT INTO tbl_stock VALUES 
('Item1', 'Mon', 110), ('Item2', 'Mon', 230), ('Item3', 'Mon', 150), 
('Item1', 'Tue', 115), ('Item2', 'Tue', 231), ('Item3', 'Tue', 162), 
('Item1', 'Wed', 110), ('Item2', 'Wed', 240), ('Item3', 'Wed', 162), 
('Item1', 'Thu', 109), ('Item2', 'Thu', 228), ('Item3', 'Thu', 145), 
('Item1', 'Fri', 120), ('Item2', 'Fri', 210), ('Item3', 'Fri', 125),
('Item1', 'Mon', 122), ('Item2', 'Mon', 225), ('Item3', 'Mon', 140),
('Item1', 'Tue', 110), ('Item2', 'Tue', 235), ('Item3', 'Tue', 154),
('Item1', 'Wed', 125), ('Item2', 'Wed', 220), ('Item3', 'Wed', 142);

Таблица должна выглядеть следующим образом:

+========+=========+=======+
|   item | weekday | price |
+========+=========+=======+
|  Item1 |    Mon  |   110 |
+--------+---------+-------+
|  Item2 |    Mon  |   230 |
+--------+---------+-------+
|  Item3 |    Mon  |   150 |
+--------+---------+-------+
|  Item1 |    Tue  |   115 |
+--------+---------+-------+
|  Item2 |    Tue  |   231 |
+--------+---------+-------+
|  Item3 |    Tue  |   162 |
+--------+---------+-------+
|          . . .           |
+--------+---------+-------+
|  Item2 |    Wed  |   220 |
+--------+---------+-------+
|  Item3 |    Wed  |   142 |
+--------+---------+-------+

Чтобы выполнить агрегацию, которая должна найти среднюю цену за элемент за каждый недельный день, мы будем использовать реляционный оператор PIVOT чтобы повернуть столбец weekday табличного выражения в агрегированные значения строк, как PIVOT ниже:

SELECT * FROM tbl_stock
PIVOT ( 
    AVG(price) FOR weekday IN ([Mon], [Tue], [Wed], [Thu], [Fri])
) pvt;

Результат:

+--------+------+------+------+------+------+
|  item  |  Mon |  Tue |  Wed |  Thu |  Fri |
+--------+------+------+------+------+------+
|  Item1 |  116 |  112 |  117 |  109 |  120 |
|  Item2 |  227 |  233 |  230 |  228 |  210 |
|  Item3 |  145 |  158 |  152 |  145 |  125 |
+--------+------+------+------+------+------+

Наконец, чтобы выполнить обратную операцию PIVOT , мы можем использовать реляционный оператор UNPIVOT для поворота столбцов в строки, как UNPIVOT ниже:

SELECT * FROM tbl_stock
PIVOT ( 
    AVG(price) FOR weekday IN ([Mon], [Tue], [Wed], [Thu], [Fri])
) pvt
UNPIVOT ( 
    price FOR weekday IN ([Mon], [Tue], [Wed], [Thu], [Fri])
) unpvt;

Результат:

+=======+========+=========+
|  item |  price | weekday |
+=======+========+=========+
| Item1 |    116 |     Mon |
+-------+--------+---------+
| Item1 |    112 |     Tue |
+-------+--------+---------+
| Item1 |    117 |     Wed |
+-------+--------+---------+
| Item1 |    109 |     Thu |
+-------+--------+---------+
| Item1 |    120 |     Fri |
+-------+--------+---------+
| Item2 |    227 |     Mon |
+-------+--------+---------+
| Item2 |    233 |     Tue |
+-------+--------+---------+
| Item2 |    230 |     Wed |
+-------+--------+---------+
| Item2 |    228 |     Thu |
+-------+--------+---------+
| Item2 |    210 |     Fri |
+-------+--------+---------+
| Item3 |    145 |     Mon |
+-------+--------+---------+
| Item3 |    158 |     Tue |
+-------+--------+---------+
| Item3 |    152 |     Wed |
+-------+--------+---------+
| Item3 |    145 |     Thu |
+-------+--------+---------+
| Item3 |    125 |     Fri |
+-------+--------+---------+

Динамический PIVOT

Одна из проблем с запросом PIVOT заключается в том, что вам нужно указать все значения внутри выбора IN если вы хотите видеть их как столбцы. Быстрый способ обойти эту проблему - создать динамический выбор IN, PIVOT динамику PIVOT .

Для демонстрации мы будем использовать таблицу Books в базе данных Bookstore . Мы предполагаем, что таблица довольно де-нормирована и имеет следующие столбцы

Table: Books
-----------------------------
BookId (Primary Key Column)
Name
Language
NumberOfPages
EditionNumber
YearOfPrint
YearBoughtIntoStore
ISBN
AuthorName
Price
NumberOfUnitsSold

Сценарий создания для таблицы будет выглядеть так:

CREATE TABLE [dbo].[BookList](
      [BookId] [int] NOT NULL,
      [Name] [nvarchar](100) NULL,
      [Language] [nvarchar](100) NULL,
      [NumberOfPages] [int] NULL,
      [EditionNumber] [nvarchar](10) NULL,
      [YearOfPrint] [int] NULL,
      [YearBoughtIntoStore] [int] NULL,
[NumberOfBooks] [int] NULL,
[ISBN] [nvarchar](30) NULL,
      [AuthorName] [nvarchar](200) NULL,
      [Price] [money] NULL,
      [NumberOfUnitsSold] [int] NULL,
 CONSTRAINT [PK_BookList] PRIMARY KEY CLUSTERED
(
      [BookId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Теперь, если нам нужно запросить базу данных и выяснить количество книг на английском, русском, немецком, хинди, латинском языках, которые каждый год покупаются в книжном магазине и представляют нашу продукцию в небольшом формате отчета, мы можем использовать запрос PIVOT, подобный этому

SELECT * FROM
  (
   SELECT YearBoughtIntoStore AS [Year Bought],[Language], NumberOfBooks
   FROM BookList
  ) sourceData 
 PIVOT
  (
  SUM(NumberOfBooks)
  FOR [Language] IN (English, Russian, German, Hindi, Latin)
  ) pivotrReport

Особый случай - когда у нас нет полного списка языков, поэтому мы будем использовать динамический SQL, как показано ниже.

DECLARE @query VARCHAR(4000)
DECLARE @languages VARCHAR(2000)
SELECT @languages =
        STUFF((SELECT DISTINCT '],['+LTRIM([Language])FROM [dbo].[BookList]
        ORDER BY '],['+LTRIM([Language]) FOR XML PATH('') ),1,2,'') + ']'
SET @query=
'SELECT * FROM
  (SELECT YearBoughtIntoStore AS [Year Bought],[Language],NumberOfBooks
   FROM BookList) sourceData
PIVOT(SUM(NumberOfBooks)FOR [Language] IN ('+ @languages +')) pivotrReport' EXECUTE(@query)