Oracle中实现行转列的方法（oracle中的行转列）

在Oracle数据库中，我们经常需要把行转列来满足需求，这种转换可以使用多种方法来实现。为了更好地了解这些方法，本篇文章将详细介绍Oracle中实现行转列的方法，并提供相应的代码实现。

1. 使用PIVOT函数

在Oracle 11g中，我们可以使用PIVOT函数来实现行转列。该函数使用聚合函数来将行转换为列，可以极大地简化查询语句。

例如，我们有以下示例表：

CREATE TABLE emp (

emp_id NUMBER,

emp_name VARCHAR2(50),

emp_title VARCHAR2(50)

);

INSERT INTO emp VALUES (1, John , Manager );

INSERT INTO emp VALUES (2, Mary , Clerk );

INSERT INTO emp VALUES (3, Bob , Clerk );

INSERT INTO emp VALUES (4, Jane , Manager );

INSERT INTO emp VALUES (5, Steve , Clerk );

现在我们想把emp_title列转换为列名，并将每个emp_name对应到对应的列上。我们可以使用以下查询语句：

SELECT *

FROM (

SELECT emp_name, emp_title

FROM emp

)

PIVOT (

COUNT(emp_title)

FOR emp_title IN ( Manager , Clerk )

);

这将返回以下结果：

EMP_NAME Manager Clerk

John 1 0

Mary 0 1

Bob 0 1

Jane 1 0

Steve 0 1

2. 使用CASE函数

在Oracle 10g及以下版本中，我们可以使用CASE函数来实现行转列。这种方法比较复杂，但对于早期版本的Oracle来说是一种非常实用的方法。

例如，我们有以下示例表：

CREATE TABLE sales (

sales_id NUMBER,

salesman VARCHAR2(50),

product VARCHAR2(50),

amount NUMBER

);

INSERT INTO sales VALUES (1, Bob , Product A , 100);

INSERT INTO sales VALUES (2, Bob , Product B , 200);

INSERT INTO sales VALUES (3, John , Product A , 150);

INSERT INTO sales VALUES (4, Mary , Product B , 250);

INSERT INTO sales VALUES (5, Mary , Product A , 50);

INSERT INTO sales VALUES (6, Bob , Product C , 300);

INSERT INTO sales VALUES (7, John , Product C , 400);

现在我们想把product列转换为列名，并将每个salesman对应到对应的列上。我们可以使用以下查询语句：

SELECT salesman,

SUM(CASE WHEN product = Product A THEN amount ELSE 0 END) AS Product A ,

SUM(CASE WHEN product = Product B THEN amount ELSE 0 END) AS Product B ,

SUM(CASE WHEN product = Product C THEN amount ELSE 0 END) AS Product C

FROM sales

GROUP BY salesman;

这将返回以下结果：

SALESMAN Product A Product B Product C

Bob 100 200 300

John 150 0 400

Mary 50 250 0

3. 使用XML函数

在Oracle 9i及以下版本中，我们可以使用XML函数来实现行转列。该方法的优点是可以处理不确定数目的列，但是需要处理XML数据类型。

例如，我们有以下示例表：

CREATE TABLE scores (

student_id NUMBER,

subject VARCHAR2(50),

score NUMBER

);

INSERT INTO scores VALUES (1, Math , 80);

INSERT INTO scores VALUES (1, Physics , 85);

INSERT INTO scores VALUES (2, Math , 90);

INSERT INTO scores VALUES (2, Physics , 95);

INSERT INTO scores VALUES (3, Math , 85);

INSERT INTO scores VALUES (3, Physics , 70);

现在我们想把subject列转换为列名，并将每个student_id对应到对应的列上。我们可以使用以下查询语句：

SELECT student_id,

extractvalue(xmltype( || SYS_CONNECT_BY_PATH(subject || , || score, , ) || ), a/ || subject) AS subject,

extractvalue(xmltype( || SYS_CONNECT_BY_PATH(subject || , || score, , ) || ), a/ || TO_CHAR(score)) AS score

FROM (

SELECT student_id, subject, score,

row_number() OVER(PARTITION BY student_id ORDER BY subject, score) AS rn,

count(*) OVER(PARTITION BY student_id) AS cnt

FROM scores

)

WHERE rn = cnt

START WITH rn = 1

CONNECT BY PRIOR student_id = student_id AND PRIOR rn + 1 = rn

ORDER BY student_id;

这将返回以下结果：

STUDENT_ID Math Physics

1 80 85

2 90 95

3 85 70

以上就是在Oracle中实现行转列的方法，从简单到复杂的三种方法已经一一介绍完毕。无论您使用哪种方法，都可以把行转列以满足您的需求。

我想要获取技术服务或软件
服务范围：MySQL、ORACLE、SQLSERVER、MongoDB、PostgreSQL 、程序问题
服务方式：远程服务、电话支持、现场服务，沟通指定方式服务
技术标签：数据恢复、安装配置、数据迁移、集群容灾、异常处理、其它问题

本站部分文章参考或来源于网络，如有侵权请联系站长。
数据库远程运维 Oracle中实现行转列的方法（oracle中的行转列）