Microsoft SQL Server
PIVOT / UNPIVOT

Syntaxis

• SELECTEER <non-pivoted column> ,
  [eerste gedraaide kolom] AS <column name> ,
  [tweede gedraaide kolom] AS <column name> ,
  ...
  [laatst gedraaide kolom] AS <column name>
  VAN
  ( <SELECT query that produces the data> )
  AS <alias for the source query>
  PIVOT
  (
  <aggregation function> ( <column being aggregated> )
  VOOR
  [ <column that contains the values that will become column headers> ]
  IN ([eerste gedraaide kolom], [tweede gedraaide kolom],
  ... [laatst gedraaide kolom])
  ) AS <alias for the pivot table> <optional ORDER BY clause> ;

Opmerkingen

Simple Pivot - Statische kolommen

Laten we met behulp van de itemverkooptabel uit de voorbeelddatabase de totale hoeveelheid berekenen die we van elk product hebben verkocht.

Dit kan eenvoudig worden gedaan met een groep door, maar laten we aannemen dat we onze resultatentabel 'roteren' op een manier dat we voor elke product-ID een kolom hebben.

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

Omdat onze 'nieuwe' kolommen getallen zijn (in de brontabel), moeten we vierkante haken []

Dit geeft ons een output als

Eenvoudige PIVOT & UNPIVOT (T-SQL)

Hieronder is een eenvoudig voorbeeld dat de gemiddelde prijs van elk artikel per weekdag toont.

Stel eerst dat we een tabel hebben die de dagelijkse prijzen van alle artikelen bijhoudt.

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

De tabel moet er hieronder uitzien:

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

Om aggregatie dat moet de gemiddelde prijs per stuk voor elke dag van de week te vinden uit te voeren, gaan we naar de relationele operator gebruiken PIVOT om de kolom te draaien weekday van tabelwaardefuncties expressie in geaggregeerde rij waarden, zoals hieronder:

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

Resultaat:

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

Ten slotte kunnen we, om de omgekeerde bewerking van PIVOT , de relationele operator UNPIVOT gebruiken om kolommen in rijen te roteren zoals hieronder:

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;

Resultaat:

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

Dynamische PIVOT

Een probleem met de PIVOT query is dat u alle waarden in de IN selectie moet opgeven als u ze als kolommen wilt zien. Een snelle manier om dit probleem te omzeilen is om een dynamische IN-selectie te maken die uw PIVOT dynamisch maakt.

Ter demonstratie zullen we een tabel te gebruiken Books in een Bookstore 's database. We nemen aan dat de tabel behoorlijk de-genormaliseerd is en de volgende kolommen heeft

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

Het creatiescript voor de tabel ziet er als volgt uit:

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

Als we nu in de database een query moeten uitvoeren en het aantal boeken in het Engels, Russisch, Duits, Hindi en Latijn moeten vinden dat elk jaar in de boekwinkel wordt gekocht en onze output in een klein rapportformaat presenteren, kunnen we de PIVOT-query als deze gebruiken

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

In het speciale geval hebben we geen volledige lijst met talen, dus gebruiken we dynamische SQL zoals hieronder

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)