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中实现行转列的方法，从简单到复杂的三种方法已经一一介绍完毕。无论您使用哪种方法，都可以把行转列以满足您的需求。
