How to convert rows to columns in Oracle-Oracle-php.cn

How to convert rows to columns in Oracle

PHPz

Release： 2023-04-18 09:44:26

Original

22280 people have browsed it

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;

Copy after login

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)
);

Copy after login

The running results are as follows:

NameSubjectScore1JohnScore1801JohnScore2701JohnScore390Score1Score2Score3Score1##3TomScore2753TomScore380

DROP TABLE score;

Copy after login

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;

Copy after login

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;

Copy after login

The running results are as follows:

Name

SubjectScoreScore180709090859560##3Tom Score2753TomScore380

DROP TABLE score;

Copy after login

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!