如何在Oracle中实现横表变纵表转换（oracle中横表变纵表）

如何在Oracle中实现横表变纵表转换

在Oracle数据库中，经常需要将横向（行）表转换为纵向（列）表或者将纵向（列）表转换为横向（行）表。这种转换通常用在数据集成、报表制作等场景中。本篇文章将介绍如何在Oracle中实现横表变纵表转换。

一、横表变纵表转换

接下来我们将通过一个简单的示例介绍如何在Oracle中实现横表变纵表转换。

假设我们有一下数据表：

“`sql

CREATE TABLE education (

id NUMBER(10) PRIMARY KEY,

name VARCHAR2(50),

degree VARCHAR2(50),

score NUMBER(10,2)

);

INSERT INTO education (id, name, degree, score) VALUES (1, ‘张三’, ‘本科’, 3.2);

INSERT INTO education (id, name, degree, score) VALUES (2, ‘张三’, ‘硕士’, 3.5);

INSERT INTO education (id, name, degree, score) VALUES (3, ‘张三’, ‘博士’, 3.8);

INSERT INTO education (id, name, degree, score) VALUES (4, ‘李四’, ‘本科’, 3.5);

INSERT INTO education (id, name, degree, score) VALUES (5, ‘李四’, ‘硕士’, 3.8);

INSERT INTO education (id, name, degree, score) VALUES (6, ‘王五’, ‘本科’, 3.8);

该表包含学历信息，其中id为学历记录的唯一标识，name为学生姓名，degree为学历水平，score为该学历水平的成绩。现在我们需要将该表转换为纵向表，即需要将“本科”、“硕士”、“博士”分别作为一列，成绩分别作为该列的数据。我们可以采用Oracle的PIVOT函数来实现该转换。

```sql
SELECT * 
FROM (
   SELECT name, degree, score 
   FROM education
)
PIVOT (
   MAX(score) FOR degree IN ('本科' AS "BACHELOR", '硕士' AS "MASTER", '博士' AS "DOCTOR")
)
ORDER BY name;

运行以上SQL语句后，将得到下面的结果：

“`

NAME BACHELOR MASTER DOCTOR

李四 3.5 3.8

王五 3.8

张三 3.2 3.5 3.8

在上述SQL语句中，首先在FROM子句中使用了一条SQL查询，该查询选择了name、degree和score三列。接下来在PIVOT子句中，使用了MAX聚合函数将score列的最大值作为该学历水平的成绩，并指定了degree列的取值范围，即本科、硕士和博士。最终的结果是将每个学生的学历转换为列，并将学历的成绩作为该列的数据。

二、纵表变横表转换

同样地，我们也可以将纵向表转换为横向表。假设我们有一下纵向表：

```sql
CREATE TABLE score (
   id      NUMBER(10) PRIMARY KEY,
   name    VARCHAR2(50),
   subject VARCHAR2(50),
   score   NUMBER(10,2)
);

INSERT INTO score (id, name, subject, score) VALUES (1, '张三', '语文', 80);
INSERT INTO score (id, name, subject, score) VALUES (2, '张三', '数学', 90);
INSERT INTO score (id, name, subject, score) VALUES (3, '张三', '英语', 85);
INSERT INTO score (id, name, subject, score) VALUES (4, '李四', '语文', 85);
INSERT INTO score (id, name, subject, score) VALUES (5, '李四', '数学', 85);
INSERT INTO score (id, name, subject, score) VALUES (6, '李四', '英语', 90);

该表记录了每个学生每个科目的成绩信息。现在我们需要将该表转换为横向表，即需要将每个学生的成绩分别作为一列，科目分别作为该列的数据。我们可以采用Oracle的LISTAGG函数来实现该转换。

“`sql

SELECT *

FROM (

SELECT name, subject, score

FROM score

)

PIVOT (

LISTAGG(score,’,’) WITHIN GROUP (ORDER BY subject)

FOR name IN (‘张三’ AS “Zhang San”, ‘李四’ AS “Li Si”)

)

运行以上SQL语句后，将得到下面的结果：

SUBJECT Zhang San Li Si

语文 80 85

数学 90 85

英语 85 90

在上述SQL语句中，首先在FROM子句中使用了一条SQL查询，该查询选择了name、subject和score三列。接下来在PIVOT子句中，使用了LISTAGG函数将每个学生的成绩用逗号分隔成一个字符串，并指定了name列的取值范围，即“张三”和“李四”。最终的结果是将每个科目转换为列，并将每个学生的成绩作为该列的数据。

总结

通过PIVOT函数和LISTAGG函数，我们可以在Oracle数据库中方便地实现横表变纵表和纵表变横表的转换。这种转换可以为数据集成、报表制作等场景提供更灵活、更高效的支持。