Microsoft SQL Server
PIVOT / UNPIVOT

Składnia

• SELECT <non-pivoted column> ,
  [pierwsza kolumna przestawna] AS <column name> ,
  [druga kolumna przestawna] AS <column name> ,
  ...
  [ostatnia kolumna przestawna] AS <column name>
  OD
  ( <SELECT query that produces the data> )
  AS <alias for the source query>
  SWORZEŃ
  (
  <aggregation function> ( <column being aggregated> )
  DLA
  [ <column that contains the values that will become column headers> ]
  IN ([pierwsza kolumna przestawna], [druga kolumna przestawna],
  ... [ostatnia kolumna przestawna])
  ) AS <alias for the pivot table> <optional ORDER BY clause> ;

Uwagi

Prosta oś obrotu - kolumny statyczne

Korzystając z tabeli sprzedaży przedmiotów z przykładowej bazy danych , obliczmy i pokażemy całkowitą ilość sprzedaną każdego produktu.

Można to łatwo zrobić za pomocą grupy, ale załóżmy, że „obrócimy” naszą tabelę wyników w taki sposób, że dla każdego identyfikatora produktu mamy kolumnę.

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

Ponieważ nasze „nowe” kolumny to liczby (w tabeli źródłowej), musimy nawiasy kwadratowe []

To da nam wynik jak

Proste PIVOT i UNPIVOT (T-SQL)

Poniżej znajduje się prosty przykład, który pokazuje średnią cenę każdego przedmiotu w ciągu tygodnia.

Po pierwsze, załóżmy, że mamy tabelę, która prowadzi codzienną ewidencję cen wszystkich przedmiotów.

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);

Tabela powinna wyglądać jak poniżej:

+========+=========+=======+
|   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 |
+--------+---------+-------+

Aby przeprowadzić agregację, która ma znaleźć średnią cenę za sztukę na każdy dzień tygodnia, użyjemy operatora relacyjnego PIVOT aby obrócić kolumnę weekday wyrażenia o wartościach tabelarycznych w zagregowane wartości wierszy, jak poniżej:

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

Wynik:

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

Wreszcie, aby wykonać odwrotną operację PIVOT , możemy użyć operatora relacyjnego UNPIVOT aby obrócić kolumny w rzędy, jak poniżej:

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;

Wynik:

+=======+========+=========+
|  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 |
+-------+--------+---------+

Dynamiczny PIVOT

Jednym z problemów z zapytaniem PIVOT jest to, że musisz określić wszystkie wartości w zaznaczeniu IN jeśli chcesz widzieć je jako kolumny. Szybkim sposobem na obejście tego problemu jest utworzenie dynamicznego wyboru IN, który sprawi, że PIVOT dynamiczny.

Do celów demonstracyjnych wykorzystamy tabelę Books w bazie danych Bookstore . Zakładamy, że tabela jest dość zdenormalizowana i zawiera następujące kolumny

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

Skrypt tworzenia tabeli będzie wyglądał następująco:

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

Teraz, jeśli musimy zapytać w bazie danych i ustalić liczbę książek kupionych w księgarni w języku angielskim, rosyjskim, niemieckim, hindi i łacińskim każdego roku i przedstawić nasze wyniki w małym formacie raportu, możemy użyć zapytania PIVOT takiego jak ten

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

Szczególny przypadek ma miejsce, gdy nie mamy pełnej listy języków, więc użyjemy dynamicznego SQL jak poniżej

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)