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.
Contact Us