How to convert Oracle horizontal table to vertical table

In Oracle, you can use the pivot() function to convert a horizontal table into a vertical table. This function is used to convert rows into columns. The syntax is "SELECT * FROM (data query set) PIVOT (SUM (row The value of the column after the conversion)FOR needs to be converted into the column IN (the value of the column after the conversion))".

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

How to convert Oracle horizontal table to vertical table

Oracle row to column conversion is to use the value of a certain field as a unique value, and then convert the row value of another field into its column value. Here we still use the student score table of our system as an example. The score table records (rows) correspond to the scores of each student in each subject. Then we need to make a report that displays all the subjects of each student as one column. performance information. The case data is as follows:

#So how do we achieve it? Here are several methods one by one:

1. First of all, what we must think of is that it should be possible to use Oracle grouping (group by). The implementation code is as follows:

select c.stuname,
       --利用分组聚合函数
       sum(decode(b.coursename, '英语(2018上学期)', t.score, 0)) as "英语(2018上学期)",
       sum(decode(b.coursename, '数学(2018上学期)', t.score, 0)) as "英语(2018上学期)",
       sum(decode(b.coursename, '语文(2018上学期)', t.score, 0)) as "英语(2018上学期)"
  from STUDENT.SCORE t, student.course b, student.stuinfo c
 where t.courseid = b.courseid
   and t.stuid = c.stuid
 group by c.stuname

We use group by to Students are grouped, and then use decode to convert the grade value of the corresponding course, and then sum it up to get the result value of the grade. The result is as follows:

2 , Oracle11g later provides the built-in function PIVOT, which can perfectly solve this row to column requirement. The specific syntax structure is as follows:

SELECT * FROM （数据查询集）
PIVOT 
(
 SUM(Score/*行转列后 列的值*/) FOR 
 coursename/*需要行转列的列*/ IN (转换后列的值)
)

The specific code is as follows:

select * from  (select c.stuname,
       b.coursename,
       t.score
  from STUDENT.SCORE t, student.course b, student.stuinfo c
 where t.courseid = b.courseid
   and t.stuid = c.stuid )  /*数据源*/
PIVOT 
(
 SUM(score/*行转列后 列的值*/) 
 FOR coursename/*需要行转列的列*/ IN ('英语(2018上学期)' as 英语,'数学(2018上学期)' as 数学,'语文(2018上学期)' as 语文 )
) ;

The result is as follows:

Recommended tutorial: "Oracle Video Tutorial"

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