Pivot y Unpivot en MSSQL

Pivot y Unpivot en MSSQL

Armando Query's con Pivot y Unpivot en MSSQL (SQLServer2008 R2)

Creamos una Base de Datos, con un esquema simple de una tabla:

USE [Nombre_Base_Datos];
GO

-- Create the table and insert values as portrayed in the previous example.
CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int,
    Emp3 int, Emp4 int, Emp5 int);
GO

Luego insertamos algunos datos:

INSERT INTO pvt VALUES (1,4,3,5,4,4);
INSERT INTO pvt VALUES (2,4,1,5,5,5);
INSERT INTO pvt VALUES (3,4,3,5,4,4);
INSERT INTO pvt VALUES (4,4,2,5,5,4);
INSERT INTO pvt VALUES (5,5,1,5,5,5);
GO

Ahora probamos Pivot (varias SubQuery's con Union) en una Query:

-- And a simple union pivot for each column...
SELECT 'Orders N° 1' AS Orders_Sorted_By_pvt,
[1], [2], [3], [4], [5]
FROM
(SELECT VendorID, Emp1
FROM pvt) AS SourceTable
PIVOT
(
AVG(Emp1)
FOR VendorID IN ([1], [2], [3], [4], [5])
) AS PivotTable
UNION
SELECT 'Orders N° 2' AS Orders_Sorted_By_pvt,
[1], [2], [3], [4], [5]
FROM
(SELECT VendorID, Emp2
FROM pvt) AS SourceTable
PIVOT
(
AVG(Emp2)
FOR VendorID IN ([1], [2], [3], [4], [5])
) AS PivotTable
UNION
SELECT 'Orders N° 3' AS Orders_Sorted_By_pvt,
[1], [2], [3], [4], [5]
FROM
(SELECT VendorID, Emp3
FROM pvt) AS SourceTable
PIVOT
(
AVG(Emp3)
FOR VendorID IN ([1], [2], [3], [4], [5])
) AS PivotTable
UNION
SELECT 'Orders N° 4' AS Orders_Sorted_By_pvt,
[1], [2], [3], [4], [5]
FROM
(SELECT VendorID, Emp4
FROM pvt) AS SourceTable
PIVOT
(
AVG(Emp4)
FOR VendorID IN ([1], [2], [3], [4], [5])
) AS PivotTable
UNION
SELECT 'Orders N° 5' AS Orders_Sorted_By_pvt,
[1], [2], [3], [4], [5]
FROM
(SELECT VendorID, Emp5
FROM pvt) AS SourceTable
PIVOT
(
AVG(Emp5)
FOR VendorID IN ([1], [2], [3], [4], [5])
) AS PivotTable;
GO

Finalmente probamos Unpivot en una Query:

-- Unpivot the table.
SELECT VendorID, Employee, Orders
FROM
   (SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
   FROM pvt) p
UNPIVOT
   (Orders FOR Employee IN
      (Emp1, Emp2, Emp3, Emp4, Emp5)
)AS unpvt;
GO