What is the method for converting one row to multiple rows in SQL Server?

1 year ago
Liam
1 minute

In SQL Server, it is possible to use the UNPIVOT operation to transform one row of data into multiple rows of data. The UNPIVOT operation is the inverse of converting column data into row data.

Here is an example demonstrating how to use UNPIVOT to transform a single row of data into multiple rows of data.

Let’s say there is a table called employees, with the following fields:

1. Employee ID, First Name, Last Name, and Salary of John Doe are as follows: 1, John, Doe, 5000

You can use the following query to convert one row of data into multiple rows of data:

SELECT employee_id, column_name, value
FROM employees
UNPIVOT
(
  value FOR column_name IN (first_name, last_name, salary)
) AS unpvt;

Running the above query will produce the following results:

1, first name, John
1, last name, Doe
1, salary, 5000

In the UNPIVOT statement, the columns first_name, last_name, and salary are transformed into row data by specifying value FOR column_name IN (first_name, last_name, salary).

bannerAds