Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://www.geeksforgeeks.org/sql-server/unpivot-in-sql-server/ below:

UNPIVOT in SQL Server - GeeksforGeeks

Last Updated : 23 Jul, 2025

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.

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;

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:

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:

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.

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4