SQL- PIVOT and UNPIVOT

You can use the PIVOT and UNPIVOT relational operators to change a table-valued expression into another table. PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output. UNPIVOT performs the opposite operation to PIVOT by rotating columns of a table-valued expression into column values.

The following is annotated syntax for PIVOT.

SELECT <non-pivoted column>,

    [first pivoted column] AS <column name>,

    [second pivoted column] AS <column name>,

    ...

    [last pivoted column] AS <column name>

FROM

    (<SELECT query that produces the data>)

    AS <alias for the source query>

PIVOT

(

    <aggregation function>(<column being aggregated>)

FOR

[<column that contains the values that will become column headers>]

    IN ( [first pivoted column], [second pivoted column],

    ... [last pivoted column])

) AS <alias for the pivot table>

<optional ORDER BY clause>;

The following code example produces a three-column table that has seven rows.

SELECT TOP 1000 [MarksID]

      ,[CustID]

      ,[Marks]

  FROM [DBSK].[dbo].[Marks]

Here is the result set.

MarksID               CustID         Marks

1                              1              234      

2                              1              3223     

3                              2              32       

4                              2              43       

5                              3              434      

6                              4              4343     

7                              4              321           

The following code displays the same result, pivoted so that the CustID values become the column headings.Now I have to get Max Msrks for Every CustID.

  select [1],[2],[3],[4] from (select CustID,Marks from Marks) as temptable

  pivot(max(Marks) for CustID in ([1],[2],[3],[4])) as MaxMarks

Here is the result set.

1                      2              3              4

3223               43           434         4343  

for the same result you can use the Different Query using temptable

  select CustID,Marks

  into #temptable

  from Marks

  select [1],[2],[3],[4] from #temptable

  pivot(max(Marks) for CustID in ([1],[2],[3],[4])) as MaxMarks

  drop table #temptable

UNPIVOT performs almost the reverse operation of PIVOT, by rotating columns into rows. 

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

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

Here is the result set of Table.

VendorID            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 

Now suppose, you want to rotate the column identifiers Emp1, Emp2, Emp3, Emp4, andEmp5 into row values that correspond to a particular vendor. This means that you must identify two additional columns. The column that will contain the column values that you are rotating (Emp1, Emp2,...) will be called Employee, and the column that will hold the values that currently reside under the columns being rotated will be called Orders. These columns correspond to the pivot_column and value_column, respectively, in the Transact-SQL definition. Here is the 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

Here is the result set.

VendorID            Employee            Orders

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

3                              Emp1                     4

……….

.......