Home  >  Article  >  Database  >  How to convert rows to columns in Oracle

How to convert rows to columns in Oracle

PHPz
PHPzOriginal
2023-04-18 09:06:5422212browse

In Oracle database, row conversion is a common data processing requirement. It converts multiple values ​​in a row into multiple values ​​in a column for better presentation and analysis of data.

For example, assume we have the following table:

##1John8070902Lily9085953Tom6075 ##If we want to convert each student's grade into a separate row, we can use row to column to achieve the following result:
ID Name Score1 Score2 Score3
##80

IDNameSubjectScore##1JohnJohnJohn##2Lily Score1902LilyScore2852LilyScore3953TomScore1603TomScore2753TomScore380Use UNPIVOT to transfer rows to columns
Score1 80 1
Score2 70 1
Score3 90
In Oracle, there are several Methods can be used to convert rows to columns, including using UNPIVOT and UNION ALL. Next, we will detail the steps and syntax of each method.
UNPIVOT is a keyword in Oracle SQL that can move columns into rows. Use UNPIVOT query to convert multiple columns into multiple rows. To use UNPIVOT, you first need to ensure that each row in the table has the same number of columns and the same data type.

The following are the steps to use UNPIVOT to convert rows to columns:

Step 1: Create a sample table

First, we need to create a sample table to prepare the data.

CREATE TABLE score (
   ID INT,
   Name VARCHAR2(20),
   Score1 INT,
   Score2 INT,
   Score3 INT
);

INSERT INTO score VALUES (1, 'John', 80, 70, 90);
INSERT INTO score VALUES (2, 'Lily', 90, 85, 95);
INSERT INTO score VALUES (3, 'Tom', 60, 75, 80);
COMMIT;
Step 2: Run the UNPIVOT query

Then, we can use the following UNPIVOT query to transform the data:

SELECT ID, Name, Subject, Score
FROM score
UNPIVOT (
   Score FOR Subject IN (Score1, Score2, Score3)
);
The running results are as follows:

ID

NameSubjectScore1JohnScore1801JohnScore2701JohnScore390Score1Score2Score3Score1##3TomScore2753TomScore380
DROP TABLE score;
Use UNION ALL to convert rows to columns
##2 Lily
90 2 Lily
85 2 Lily
95 3 Tom
60
Step 3: Clear the table Finally, we can clear the data by deleting the table:
UNION ALL too A method used in Oracle SQL to convert rows to columns. Columns can be converted into rows using a UNION ALL query. The following are the steps to use UNION ALL to convert rows to columns:

Step 1: Create a sample table

First, we need to create a sample table to prepare the data.

CREATE TABLE score (
   ID INT,
   Name VARCHAR2(20),
   Score1 INT,
   Score2 INT,
   Score3 INT
);

INSERT INTO score VALUES (1, 'John', 80, 70, 90);
INSERT INTO score VALUES (2, 'Lily', 90, 85, 95);
INSERT INTO score VALUES (3, 'Tom', 60, 75, 80);
COMMIT;
Step 2: Run UNION ALL query

Then, we can use the following UNION ALL query to transform the data:

SELECT ID, Name, 'Score1' Subject, Score1 Score FROM score
UNION ALL
SELECT ID, Name, 'Score2' Subject, Score2 Score FROM score
UNION ALL
SELECT ID, Name, 'Score3' Subject, Score3 Score FROM score
ORDER BY ID, Subject;
The running results are as follows:

ID

Name

SubjectScoreScore180709090859560##3Tom Score2753TomScore380
DROP TABLE score;
Row to column is a common Data processing technology allows us to better display and analyze data. In Oracle SQL, we can use UNPIVOT and UNION ALL to convert rows to columns. By mastering these concepts and related syntax, we can easily process and analyze data in the database.
##1 John
1 John Score2
1 John Score3
2 Lily Score1
2 Lily Score2
2 Lily Score3
3 Tom Score1
Step 3: Clear the table Finally, we can clear the data by deleting the table: Summary

The above is the detailed content of How to convert rows to columns in Oracle. For more information, please follow other related articles on the PHP Chinese website!

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn