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

Microsoft SQL Server Training Online Learning Classes Advance SELECT Operations Pivot UnPivot operators

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

  3. 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: Amxn to AT=Anxm


    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

    4 comments:

    1. I am very glad to read your informative blog...thanks a lot for your valuable sharing
      you can also visit here .net corporate training gurgaon

      ReplyDelete
    2. It's really excellent blog, I just share your blog because it's really nice. Just look at this MSBI Online Training

      ReplyDelete
    3. Thanks for sharing such a useful information. It is really impressive and useful. I like your blog and suggested to my friends too. Keep sharing. If anyone wants to learn Dot net or .Net course online, please visit below site:
      dotnet Online Training, dotnet course, dotnet online training in kurnool, dotnet online training in hyderabad, dotnet online training in bangalore, online courses, online learning, online education, trending courses, best career courses

      ReplyDelete