How to achieve row-column conversion in oracle

Methods to implement row-column conversion: 1. Use the PIVOT() function to implement row-to-column conversion, with the syntax "SELECT * FROM (data set) PIVOT (SUM(Score) FOR coursename IN (converted column value))" "; 2. Use the unpivot() function to realize column switching.

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

Oracle row to column

Oracle row to column is to use the value of a certain field as the only value, and then use the row value of another field Convert to 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:

Oracle column to row conversion

Oracle column to row conversion is to convert the fields of the columns in a row according to the unique value of the row Convert to multiple rows of data. For example, the basic data of the student performance table in the Oracle row to column conversion above is that one student's subject performance corresponds to one record. 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 Tutorial"

The above is the detailed content of How to achieve row-column conversion in oracle. For more information, please follow other related articles on the PHP Chinese website!