UNPIVOT in SQL Server

In SQL Server, Unpivot is a relational operator that allows us to convert columns into rows in a table. It’s far the reverse of the Pivot operation, which is used to transform rows into columns. Unpivot is typically used when we have a table with multiple columns, and we need to restructure the information to make it more to be had for evaluation or reporting.

UNPIVOT Operation

1. Identify the columns that you want to unpivot.
2. Create a query that specifies the columns you need to unpivot and the columns that should stay constant.
3. Use the UNPIVOT keyword to transform the Table.

Syntax:

SELECT id, columnName, value
FROM
(SELECT id, column1, column2, column3, ... 
 FROM TableName) AS SourceTable
UNPIVOT
(value FOR columnName IN (column1, column2, column3, ...)) AS Alias;

Example 1:

Let’s create a simple table named “Student” with columns for different subjects and their corresponding marks. then unpivot this data to create a more flexible structure for analysis.

CREATE TABLE Student (
    StudentID INT,
    Math INT,
    Science INT,
    English INT
);

INSERT INTO Student (StudentID, Math, Science, English)
VALUES (1, 70, 80, 90),
               (2, 90, 55, 60),
               (3, 80, 70, 90),
               (4, 75, 65, 80);

SELECT * FROM Student;

Output:

Student table

Now, applying UNPIVOT operator to this data:

SELECT StudentID, [SubjectNames], Marks
FROM (
    SELECT StudentID, Math, Science, English
    FROM Student
) AS s
UNPIVOT
(
    Marks FOR [SubjectNames] IN (Math, Science, English)
) AS unpvt;

After using UNPIVOT operator, we get the following result:

Student table after UNPIVOT operation

Example 2: Unpivoting Employee Data

Let’s create a table named “EmployeeData” with various attributes stored as columns, and then unpivot this data to create a more flexible structure for analysis.

CREATE TABLE EmployeeData (
    EmployeeID INT,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Department VARCHAR(50)
);

INSERT INTO EmployeeData (EmployeeID, FirstName, LastName, Department)
VALUES
    (1, 'Sawai', 'Singh', 'IT'),
    (2, 'Nikhil', 'Kumar', 'HR'),
    (3, 'Ravi', 'Soni', 'Finance');

SELECT * FROM EmployeeData

Output:

EmployeeData table

Now, applying UNPIVOT operator to this data:

SELECT EmployeeID, EmployeeDetails, Value
FROM
   (SELECT EmployeeID, FirstName, LastName, Department
    FROM EmployeeData) e
UNPIVOT
   (Value FOR EmployeeDetails IN
      (FirstName, LastName, Department)
) AS unpvt;

After using UNPIVOT operator, we get the following result:

EmployeeData table after UNPIVOT operation

Conclusion

In conclusion, the UNPIVOT operation in SQL Server is a effective tool for transforming data from a wide format (pivot) into a long format, facilitating data evaluation and reporting. It lets in us to convert multiple columns into rows, making it easier to work with and extract treasured insights from our data.