How to convert Oracle vertical table to horizontal table

In Oracle, you can use the unpivot() function to convert a vertical table into a horizontal table. This function is used to convert the fields of the columns in a row into multiple rows of data according to the unique value of the row. The syntax is " select field from data set unpivot(column value for custom column name in(column name))".

The operating environment of this tutorial: Windows 10 system, Oracle 11g version, Dell G3 computer.

How to convert Oracle vertical table to horizontal table

Oracle column conversion is to convert the fields of the columns in a row into multiple rows of data according to the unique value of the row.

The basic data of the student performance table is a record corresponding to a student's subject performance. Then we converted the rows into columns and turned it into a record corresponding to the scores of a student in each column (math, English, Chinese). So this article is to convert the previously converted student score table (backup table score_copy) into columns and rows again and convert it into original data. The case data is as follows:

So how do we implement column switching? Two commonly used methods are introduced below:

1. Use union all for splicing, which can perfectly convert the corresponding columns into row records. The specific code is as follows:

select t.stuname, '英语' as coursename ,t.英语 as score  from SCORE_COPY t
union all
select t.stuname, '数学' as coursename ,t.数学 as score  from SCORE_COPY t
union all
select t.stuname, '语文' as coursename ,t.语文 as score  from SCORE_COPY t

The results are as follows:

2. This problem can also be perfectly solved by using Oracle's own column-to-row function unpivot. The specific syntax structure is as follows:

select 字段 from 数据集
unpivot（自定义列名/*列的值*/ for 自定义列名 in（列名））

The implementation code is as follows:

select stuname, coursename ,score from
score_copy  t
unpivot
(score for coursename in (英语,数学,语文))

The results are as follows:

Recommended tutorial: "Oracle Video Tutorial"

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