SqlTutorial-1 : Lesson-5 : Class-1 : Part-2 Microsoft SQL Server Training: Free Online-Learning Classes (OLC) for T-SQL Advanced-SELECTs: Pivot / UnPivot operator

Pivot and Unpivot are cross-tabular operators that transform (or rotate) a table column into rows or vice versa respectively.

INDEX:

1. PIVOT
2. UnPIVOT
3. Advanced PIVOT and UNPIVOT Examples
4. Feature Article related to PIVOT and UnPIVOT

• Pivot and Unpivot are introduced in SQL Server 2005 that performs Cross-Tabular Transformation i.e., transforms a column value into row values or vice versa
• Main usage these operators is to review cross-tabular analysis report.
• Unpivot is reverse operation of PIVOT but not exact always; i.e., In Some cases it is possible that unpviot generates exact reverse of pivot, in some cases it is not, the reason is the aggregation of values while pivoting. Pivot selects aggregate value; see syntax; Therefore, If the column value of a table to be pivot assigned with multiple values; then Unpivot is would not reverse of PIVOT.
• You can use Pivot and Unpivot simultaneously in one select query.

1. PIVOT Operator

Pivot Operator transforms a single-column into a row; For multiple column-transformation use multiple pivots operations simultaneously.

Syntax:

SELECT <non-pivoted column> ,
     [PivotedColumnValue1] AS <column name> ,
     [PivotedColumnValue2] AS <column name> ,
        ...
FROM
     ( <SELECT query that produces the data> ) AS < alias srcTable >
 PIVOT
     ( <aggregation function>( <column being aggregated> )
         FOR [<column values that will become column headers>]
         IN ( [PivotedColumnValue1] , [PivotedColumnValue2], ...   )
      ) AS <alias pvtTable>
[<optional ORDER BY clause>]

Example:

1. Simple Example-1

   Table: SourceTable Cust Product Qty Kate Veg 2 Kate Soda 6 Kate Milk 1 Kate Beer 12 Fred Milk 3 Fred Beer 24 Kate Veg 3

   =>

   Table: ResultSet Product Fired Kate BEER 24 12 MILK 3 1 SODA NULL 6 VEG NULL 5

   
   
   SELECT PRODUCT, FRED, KATE
   FROM (
                       SELECT PRODUCT, CUST, QTY
                       FROM Product
               ) AS SrcTable
   PIVOT(
                       SUM(QTY)
                       FOR CUST IN (FRED, KATE)
              ) AS pvtTable
   ORDER BY PRODUCT
   
   

2. Simple Example-2

Table: SoruceTable EmpId Cid Value 101 C1 Xyz 101 C2 Hyd 101 C3 10000 102 C1 Abc 102 C2 Sec 103 C3 15000

=>

Table: PIVOT ResultSet EmpID C1 C2 C3 101 Xyz Hyd 10000 102 Abc Sec 15000 ... ... ... ...



Select EmpID, C1,C2,C3
FROM (Select Empid,Cid, Value
              FROM Employee)
PIVOT( Max(Value) FOR Cid IN (C1,C2,C3) )


PivotData column is of String type, therefore use MAX()/MIN() function. SUM()/AVG() are invalid over Stringtype

Pivoting plays over only 3-columns data

2. UnPIVOT

UnPivot transforms table rows into columns.

• It is reverse operation to PIVOT but not exact always;
• Syntactically, UnPivot is similar to PIVOT except it does not use aggregate function and begins with UnPIVOT keyword.

Syntax:

SELECT <non-pivoted column> ,
     [PivotedColumnValue1] AS <column name> ,
     [PivotedColumnValue2] AS <column name> ,
        ...
FROM
     ( <SELECT query that produces the data> ) AS <alias for the source query>
UNPIVOT
     (  <columnName >
         FOR [<column that contains the values that will become row headers>]
         IN ( [PivotedColumnValue1] , [PivotedColumnValue2], ...   )
      ) AS <alias for the pivot table>
[<optional ORDER BY clause>]

Examples

1. Unpivoting following table

   Table: Source Table Vendor Id Emp1 Emp2 Emp3 Emp4 Emp5 1 4 3 5 4 4 2 4 1 5 5 5 3 4 3 5 4 4 4 4 2 5 5 4 5 5 1 5 5 5

   =>

   Table: UnPIVOT - ResultSet Vendor Id Employees Order 1 Emp1 4 1 Emp2 3 1 Emp3 5 1 Emp4 4 1 Emp5 4 2 Emp1 4 2 Emp2 1 2 Emp3 5 2 Emp4 5 2 Emp5 5

   
   
   SELECT VendorID, Employee, Orders
   FROM ( SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
                  FROM SourceTable) as srcTable
   UNPIVOT ( Orders FOR Employee IN (Emp1, Emp2, Emp3, Emp4, Emp5) ) as UnpTable

2. UnPIVOT is not always reverse of PIVOT
   i.e.,Original Table != PIVOT + Unpivot Table.

   
   
   

3. Advanced PIVOT and UNPIVOT Operations

1. Matrix Transposition: A^mxn to A^T=A^nxm

For this transformation, you need to first unpivot then pivot the result. Because, Pivoting works only on three columns but here 6 columns are there and Unpivoting does not summarizes the data instead it expands the data.
Let us consider a 5x5 matrix;

Table1: MatrixTable(input)
Table2: UnPivot ResultSet (Intermediate)
Table3: PIVOT + UNIPOT Result-Set (OUTPUT)

WITH ctePivotTable (RowId,Columns,[Values]) as
(
            SELECT RowID, Columns, [Values]
            FROM       (  SELECT RowID, C1, C2, C3, C4, C5 FROM TableMatrix ) as SrcTable
            UNPIVOT (  [Values] FOR Columns IN ([C1],[C2],[C3],[C4],[C5]) ) as unPvtTable
)
Select [Columns], R1,R2,R3,R4,R5
From  ( Select RowId, Columns, [Values] From ctePivotTable  ) as srcTable
PIVOT  (  Max([Values]) for RowId in (R1,R2,R3,R4,R5)    ) as pvtTable ;

4. Feature articles related to PIVOT and UnPIVOT

1. How I Remember PIVOT and UNPIVOT Operation
2. How can we PIVOT two columns simultaneously