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
- Identify the columns that you want to unpivot.
- Create a query that specifies the columns you need to unpivot and the columns that should stay constant.
- 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:
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:
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:
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:
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.