PIVOT and UNPIVOT Operator in SQL Server – C# Example Code

PIVOT and UNPIVOT Operator in SQL Server – C# Example Code

PIVOT and UNPIVOT both are relational operators in SQL servers.

These both are used to convert a table expression in an SQL server.

PIVOT operator is used to transfer your data from row-level to columns level.

UNPIVOT is the reverse of the PIVOT function. it is used to transfer data from the column level to the row level in SQL Server

Insert records in the table with this script.

INSERT INTO Employee
SELECT 'Mihir',2012,10 UNION ALL
SELECT 'Jigna',2012,50 UNION ALL
SELECT 'Harshad',2012,88 UNION ALL
SELECT 'Jay',2014,74 UNION ALL
SELECT 'Mihir',2014,85 UNION ALL
SELECT 'Jay',2014,95 UNION ALL
SELECT 'Harshad',2015,65 UNION ALL
SELECT 'Jigna',2015,45 UNION ALL
SELECT 'Mihir',2012,85;

Now we will check the data with this query, you will get the result below image.

SELECT * FROM Employee; 

Output:

Now we start understanding the PIVOT and UNPIVOT operators with this SQL table.

SQL PIVOT:

Typically, this SQL Pivot is used when processing data in stored procedures and functions.

Specifically, it is a relational operator that allows us to convert row data into column data. Also, we can perform aggregate operations on it.

The PIVOT tables return the unique value of the columns of the table.

Syntax:

SELECT 
FROM 
(    
) AS SourceTable
PIVOT
(
()
 FOR 
IN ([pivoted_column1], [pivoted_column2], [pivoted_column3] /* list of pivoted columns */)
) AS PivotTable;

Example:

SELECT 
    [Year],
    ISNULL(Mihir, 0) AS Mihir,
    ISNULL(Jigna, 0) AS Jigna,
    ISNULL(Harshad, 0) AS Harshad 
FROM 
(
    SELECT [Name], [Year], CreditScore 
    FROM Employee
) AS Table1
PIVOT
(
    SUM(CreditScore) FOR [Name] IN ([Mihir], [Jigna], [Harshad])
) AS Table2
ORDER BY [Year];

Output:

In the above result you can we have calculated the sum of the credit score for Mihir, Jigna, and Harshad employees corresponding to year value.

SQL UNPIVOT

Unpivot is the reverse process of PIVOT, it converts column-level data to row-level. for an unpivot example we are going to declare one temp variable from the above Employee table.

Example:

DECLARE @EmpTemp TABLE
([Year] INT,
Mihir INT,
Jigna INT,
Harshad INT
)

Now we are inserting the rows into that temp variable with the below query.

INSERT INTO @EmpTemp
SELECT [Year], Mihir,Jigna,Harshad FROM
(SELECT [Name], [Year] , CreditScore FROM Employee )TABLE1
PIVOT
(
SUM(CreditScore ) FOR Name IN (Mihir,Jigna,Harshad)) AS TABLE2
ORDER BY [TABLE2].[Year] 

Output:

So, you use PIVOT and UNPIVOT in SQL within stored procedures to process data.

Hope you liked the example used and the sample query.

Thank you for reading.