Oracle数据库中行列转换
Oracle数据库中行列转换包括CASE语句和PIVOT及UNPIVOT
在Oracle数据库中，行转列通常涉及到将表中的多行数据转换为列的形式，这种操作在数据报表和分析中非常常见。Oracle数据库没有直接的函数或命令可以直接完成这种转换，但可以通过几种不同的方式来实现，包括使用CASE语句、PIVOT操作以及动态SQL。下面是一些实现行转列的方法：
一、行转列
1. 使用CASE语句
我们先查询出每个职位在每个部门中的的总薪资数据
SELECT job,deptno,SUM(sal) AS sum_sal
FROM scott.emp
WHERE deptno IS NOT NULL
GROUP BY job,deptno
ORDER BY job, deptno;
JOB DEPTNO SUM_SAL
--------- ------ ----------
ANALYST 20 6000
CLERK 10 1300
CLERK 20 1900
CLERK 30 950
MANAGER 10 2450
MANAGER 20 2975
MANAGER 30 2850
PRESIDENT 10 5000
SALESMAN 30 5600
CASE语句可以在SELECT查询中根据条件将行数据转换为列。这种方法在处理固定数量的列时非常有效。
SELECT job,
SUM(CASE deptno WHEN 10 THEN sal ELSE 0 END) deptno_10_sal,
SUM(CASE deptno WHEN 20 THEN sal ELSE 0 END) deptno_20_sal,
SUM(CASE deptno WHEN 30 THEN sal ELSE 0 END) deptno_30_sal
FROM scott.emp
GROUP BY job;
-- 执行结果集
JOB DEPTNO_10_SAL DEPTNO_20_SAL DEPTNO_30_SAL
--------- ------------- ------------- -------------
CLERK 1300 1900 950
SALESMAN 0 0 5600
PRESIDENT 5000 0 0
MANAGER 2450 2975 2850
ANALYST 0 6000 0
2. 使用PIVOT操作
Oracle 11g及以后版本引入了PIVOT操作符，专门用于将行数据转换为列数据。它使得转换过程更加简洁和直观。
PIVOT 函数的语法是
SELECT ...
FROM ...
PIVOT
( pivot_clause
pivot_for_clause
pivot_in_clause )
WHERE ...
其中：
- pivot_clause 表示对某个列进行聚合运算
- pivot_for_clause 表示分组的以及需要行转列的列
- pivot_in_clause 表示行转列的列名取值范围
PIVOT 语句是在 FROM 子句和 WHERE 子句的中间，我们可以通过 WHERE 子句对行转列后的结果集进行过滤，如下：
WITH cte_data AS (
SELECT job, deptno, sal
FROM scott.emp
WHERE deptno IS NOT NULL
)
SELECT *
FROM cte_data
PIVOT (
SUM(sal) -- pivot_clause
FOR deptno -- pivot_for_clause
IN (10,20,30) -- pivot_in_clause
);
-- 执行结果集
JOB 10 20 30
--------- ---------- ---------- ----------
CLERK 1300 1900 950
SALESMAN 5600
PRESIDENT 5000
MANAGER 2450 2975 2850
ANALYST 6000
从上面的输出可以看出，行转列的列名都是数字，我们可以给这些列名指定别名，并指定where 条件如下：
WITH cte_data AS (
SELECT job, deptno, sal
FROM scott.emp
WHERE deptno IS NOT NULL
)
SELECT *
FROM cte_data
PIVOT (
SUM(sal) -- 对 sal 进行求和聚合
FOR deptno -- 需要对 deptno 进行行转列
IN (10 AS DEPTNO_10_sal,
20 AS DEPTNO_20_sal,
30 AS DEPTNO_30_sal) -- 行转列后的列名取值范围
)
WHERE job IN ('ANALYST','CLERK','SALESMAN');
-- 执行结果集
JOB DEPTNO_10_SAL DEPTNO_20_SAL DEPTNO_30_SAL
--------- ------------- ------------- -------------
CLERK 1300 1900 950
SALESMAN 5600
ANALYST 6000
注意：PIVOT中的IN子句列出了你想要转换为列的所有可能的deptno值。
在聚合的时候我们可以加多个聚合函数，如下
WITH cte_data AS (
SELECT job, deptno, sal
FROM scott.emp
WHERE deptno IS NOT NULL
)
SELECT *
FROM cte_data
PIVOT (
SUM(sal) AS sum_sal, -- 对 sal 进行求和聚合
COUNT(sal) AS cnt -- 对 sal 进行求总数聚合
FOR deptno -- 需要对 deptno 进行行转列
IN (10 AS DEPTNO_10_sal,
20 AS DEPTNO_20_sal,
30 AS DEPTNO_30_sal) -- 行转列后的列名取值范围
)
WHERE job IN ('ANALYST','CLERK','SALESMAN');
-- 执行结果集
JOB DEPTNO_10_SAL_SUM_SAL DEPTNO_10_SAL_CNT DEPTNO_20_SAL_SUM_SAL DEPTNO_20_SAL_CNT DEPTNO_30_SAL_SUM_SAL DEPTNO_30_SAL_CNT
--------- --------------------- ----------------- --------------------- ----------------- --------------------- -----------------
CLERK 1300 1 1900 2 950 1
SALESMAN 0 0 5600 4
ANALYST 0 6000 2 0
pivot_for_clause 也可以使用多个字段，如下：
WITH cte_data AS (
SELECT job, deptno, sal
FROM scott.emp
WHERE deptno IS NOT NULL
)
SELECT *
FROM cte_data
PIVOT (
SUM(sal) AS sum, -- 对 sal 进行求和聚合
COUNT(sal) AS cnt -- 对 sal 进行求总数聚合
FOR (deptno, job) -- 需要对 deptno 和 job 进行行转列
IN ((30, 'ANALYST') AS deptno_30_analyst,
(30, 'CLERK') AS deptno_30_clerk,
(30, 'SALESMAN') AS deptno_30_salesman) -- 行转列后的列名取值范围
);
-- 执行结果集
DEPTNO_30_ANALYST_SUM DEPTNO_30_ANALYST_CNT DEPTNO_30_CLERK_SUM DEPTNO_30_CLERK_CNT DEPTNO_30_SALESMAN_SUM DEPTNO_30_SALESMAN_CNT
--------------------- --------------------- ------------------- ------------------- ---------------------- ----------------------
0 950 1 5600 4
3. 动态SQL
当列的数量或名称在查询执行时未知时（例如，基于表中数据的动态变化），可以使用动态SQL来构建并执行PIVOT查询或CASE语句。这通常涉及使用PL/SQL来编写一个能够动态构建并执行SQL语句的程序。
结论
选择哪种方法取决于你的具体需求，比如列的数量是否固定、是否需要在运行时动态确定列等。对于简单的固定列转换，CASE语句或PIVOT操作可能就足够了。对于更复杂的动态列转换，你可能需要使用动态SQL。
二、列转行
Oracle数据库中的列转行操作，即将表中的列数据转换成行数据，是一种常见的数据处理需求。以下是一些实现Oracle列转行的方法：
准备测试表及数据
CREATE table scott.t_pivoted_emp
AS
SELECT * FROM (
WITH cte_data AS (
SELECT job, deptno, sal
FROM scott.emp
WHERE deptno IS NOT NULL
)
SELECT *
FROM cte_data
PIVOT (
SUM(sal) sum
FOR deptno
IN (10 AS deptno_10_sal,
20 AS deptno_20_sal,
30 AS deptno_30_sal)
)
);
SQL> select * from scott.t_pivoted_emp;
JOB DEPTNO_10_SAL_SUM DEPTNO_20_SAL_SUM DEPTNO_30_SAL_SUM
--------- ----------------- ----------------- -----------------
CLERK 1300 1900 950
SALESMAN 5600
PRESIDENT 5000
MANAGER 2450 2975 2850
ANALYST 6000
1. 使用CASE语句和UNION ALL
当UNPIVOT函数不满足需求时，可以使用CASE语句结合UNION ALL来实现更复杂的列转行。这种方法虽然比较繁琐，但灵活性更高。
SELECT * FROM (
SELECT job, 10 as deptno,DEPTNO_10_SAL_SUM as salary FROM scott.t_pivoted_emp
UNION
SELECT job, 20 as deptno,DEPTNO_20_sal_sum as salary FROM scott.t_pivoted_emp
UNION
SELECT job, 30 as deptno,DEPTNO_30_sal_sum as salary FROM scott.t_pivoted_emp
)
WHERE salary IS NOT NULL
ORDER BY job, deptno;
JOB DEPTNO SALARY
--------- ---------- ----------
ANALYST 20 6000
CLERK 10 1300
CLERK 20 1900
CLERK 30 950
MANAGER 10 2450
MANAGER 20 2975
MANAGER 30 2850
PRESIDENT 10 5000
SALESMAN 30 5600
9 rows selected
这种方法会为每一列生成一个行集合，其中包含了原始列的名称和值。
2. 使用UNPIVOT函数
UNPIVOT是Oracle提供的一个专门用于列转行的函数。它可以将表中的列转换为行，并且指定哪些列需要被转换。基本语法如下：
SELECT job,deptno,salary
FROM scott.t_pivoted_emp
UNPIVOT
(salary
FOR deptno
IN (DEPTNO_10_SAL_SUM AS 10,DEPTNO_20_SAL_SUM AS 20,DEPTNO_30_SAL_SUM AS 30))
ORDER BY job, deptno;
JOB DEPTNO SALARY
--------- ---------- ----------
ANALYST 20 6000
CLERK 10 1300
CLERK 20 1900
CLERK 30 950
MANAGER 10 2450
MANAGER 20 2975
MANAGER 30 2850
PRESIDENT 10 5000
SALESMAN 30 5600
在这个例子中，DEPTNO_10_SAL_SUM，DEPTNO_20_SAL_SUM和 DEPTNO_30_SAL_SUM会被转换成行，而JOB和转换后的值（通过value列表示）会保持为列。
3. 自定义函数和过程
对于非常特定的需求，可能需要编写自定义的PL/SQL函数或过程来实现列转行。这种方法提供了最大的灵活性，但也需要相应的编程技能和对Oracle PL/SQL的深入理解。
总结
在实际应用中，应根据具体需求和数据结构选择最合适的列转行方法。对于大多数常见场景，UNPIVOT函数和CASE语句结合UNION ALL是两种简单且有效的解决方案。对于更复杂的场景，可能需要考虑使用CONNECT BY LEVEL或编写自定义函数。
腾讯云面向开发者汇聚海量精品云计算使用和开发经验，营造开放的云计算技术生态圈。
更多推荐
38
0- 0
已为社区贡献7条内容
所有评论(0)