Microsoft SQL Server
PIVOTE / UNPIVOT

Sintaxis

• SELECCIONAR <non-pivoted column> ,
  [primera columna pivotada] AS <column name> ,
  [segunda columna pivotada] AS <column name> ,
  ...
  [última columna pivotada] AS <column name>
  DESDE
  ( <SELECT query that produces the data> )
  AS <alias for the source query>
  PIVOTE
  (
  <aggregation function> ( <column being aggregated> )
  PARA
  [ <column that contains the values that will become column headers> ]
  IN ([primera columna pivotada], [segunda columna pivotada],
  ... [última columna pivotada])
  ) AS <alias for the pivot table> <optional ORDER BY clause> ;

Observaciones

Pivote simple - columnas estáticas

Usando la tabla de ventas de artículos de la base de datos de ejemplo , calculemos y mostremos la cantidad total que vendimos de cada producto.

Esto se puede hacer fácilmente con un grupo, pero supongamos que "giramos" nuestra tabla de resultados de manera que para cada ID de producto tengamos una columna.

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

Dado que nuestras columnas 'nuevas' son números (en la tabla de origen), necesitamos corchetes []

Esto nos dará una salida como

Simple PIVOT & UNPIVOT (T-SQL)

A continuación se muestra un ejemplo simple que muestra el precio promedio de cada artículo por día de la semana.

Primero, suponga que tenemos una tabla que mantiene registros diarios de los precios de todos los artículos.

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

La tabla debe verse como a continuación:

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

Para realizar la agregación, que consiste en encontrar el precio promedio por artículo para cada día de la semana, usaremos el operador relacional PIVOT para rotar la columna weekday de la weekday de la expresión con valores de tabla en valores de fila agregados como se muestra a continuación:

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

Resultado:

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

Por último, para realizar la operación inversa de PIVOT , podemos usar el operador relacional UNPIVOT para rotar las columnas en filas como se muestra a continuación:

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;

Resultado:

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

PIVOTE Dinámico

Un problema con la consulta PIVOT es que debe especificar todos los valores dentro de la selección IN si desea verlos como columnas. Una forma rápida de evitar este problema es crear una selección IN dinámica que haga que su PIVOT dinámico.

Para demostración usaremos una tabla Books en la base de datos de una Bookstore . Suponemos que la tabla está bastante des-normalizada y tiene las siguientes columnas

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

El script de creación para la tabla será como:

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

Ahora, si necesitamos consultar en la base de datos y calcular el número de libros en inglés, ruso, alemán, hindi y latinos que compramos en la librería cada año y presentar nuestra producción en un formato de informe pequeño, podemos usar la consulta PIVOT de esta manera.

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

El caso especial es cuando no tenemos una lista completa de los idiomas, por lo que usaremos SQL dinámico como se muestra a continuación

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)