Microsoft SQL Server
PIVOT / UNPIVOT

Syntax

• SELECT <non-pivoted column>
  [erste geschwenkte Spalte] AS <column name> ,
  [zweite geschwenkte Spalte] AS <column name> ,
  ...
  [letzte geschwenkte Spalte] AS <column name>
  VON
  ( <SELECT query that produces the data> )
  AS <alias for the source query>
  PIVOT
  (
  <aggregation function> ( <column being aggregated> )
  ZUM
  [ <column that contains the values that will become column headers> ]
  IN ([erste Schwenksäule], [zweite Schwenksäule],
  ... [letzte geschwenkte Spalte])
  ) AS <alias for the pivot table> <optional ORDER BY clause> ;

Bemerkungen

Simple Pivot - Statische Spalten

Lassen Sie uns mithilfe der Artikelverkaufstabelle aus der Beispieldatenbank die von jedem Produkt verkaufte Gesamtmenge berechnen und anzeigen.

Dies kann leicht mit einer Gruppe durch erledigt werden, nehmen wir jedoch an, dass wir unsere Ergebnistabelle so "drehen", dass für jede Produkt-ID eine Spalte vorhanden ist.

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

Da unsere 'neuen' Spalten Zahlen sind (in der Quelltabelle), müssen eckige Klammern []

Dies gibt uns eine Ausgabe wie

Einfaches PIVOT & UNPIVOT (T-SQL)

Im Folgenden finden Sie ein einfaches Beispiel, das den durchschnittlichen Artikelpreis für jeden Artikel pro Wochentag zeigt.

Angenommen, wir haben eine Tabelle, in der die Preise aller Artikel täglich aufgezeichnet werden.

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

Die Tabelle sollte wie folgt aussehen:

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

Um eine Aggregation durchzuführen, mit der der Durchschnittspreis pro Artikel für jeden Wochentag ermittelt werden soll, verwenden wir den relationalen Operator PIVOT , um den Spalten- weekday des Ausdrucks in Tabellenwerten wie folgt in aggregierte PIVOT zu drehen:

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

Ergebnis:

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

Um die umgekehrte Operation von PIVOT auszuführen, können wir den relationalen Operator UNPIVOT , um Spalten wie UNPIVOT in Zeilen zu drehen:

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;

Ergebnis:

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

Dynamisches PIVOT

Ein Problem bei der PIVOT Abfrage besteht darin, dass Sie alle Werte in der IN Auswahl angeben müssen, wenn Sie sie als Spalten anzeigen möchten. Sie können dieses Problem schnell umgehen, indem Sie eine dynamische IN-Auswahl erstellen, die Ihren PIVOT dynamisiert.

Zur Demonstration verwenden wir eine Tabelle Books in der Datenbank eines Bookstore . Wir gehen davon aus, dass die Tabelle nicht normalisiert ist und folgende Spalten enthält

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

Das Erstellungsskript für die Tabelle sieht folgendermaßen aus:

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

Wenn wir nun die Datenbank abfragen und die Anzahl der Bücher in den Sprachen Englisch, Russisch, Deutsch, Hindi und Latein herausfinden müssen, die jedes Jahr im Buchladen gekauft wurden, und unsere Ausgabe in einem kleinen Berichtsformat präsentieren, können wir die PIVOT-Abfrage wie folgt verwenden

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

Sonderfall ist, wenn wir keine vollständige Liste der Sprachen haben, also verwenden wir dynamisches SQL wie folgt

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)