oracle动态交叉表

有一张表
ID NAME QUARTER QUANTITY
1 开发部 春天 10000
2 开发部 夏天 50000
3 开发部 秋天 30000
4 开发部 冬天 20000
5 销售部 春天 10000
6 销售部 夏天 4000
7 销售部 秋天 30000
8 销售部 冬天 20000

想要转换为

QUARTER 开发部 销售部
夏天 50000 4000
春天 10000 10000
秋天 30000 30000
冬天 20000 20000

如果已知部门可以写静态sql:

select quarter ,sum(decode(name,'开发部',quantity,0)) 开发部,
sum(decode(name,'销售部',quantity,0)) 销售部
from department group by quarter


如果部门未知的话,可以写动态sql,因为oracle中返回结果集过于麻烦,所以可以通过建立临时表,或者是视图的方式,间接再查询结果集,所以我拼装执行的sql有一个建立视图的动作:

declare
cv_sql varchar2(5000):=' ';
cn_number number;
cn_i number :=0;
sql_query varchar2(5000);
begin
for v_cur in (select distinct name into cn_number from department)
loop
cv_sql:= cv_sql||',sum(decode(name,'''|| v_cur.name ||''',quantity,0)) '|| v_cur.name;
end loop;
sql_query:='create or replace view query_zjx_v as select quarter'||cv_sql||' from department group by quarter';
dbms_output.put_line(sql_query);
execute immediate sql_query;
end;

### Oracle 中实现交叉表查询的功能 在 Oracle 数据库中,交叉表查询通常用于将行数据转换为列显示或将列数据转换为行显示。这种查询方式常被称为 **Pivot 查询** 或者 **Unpivot 查询**。以下是两种主要的实现方法及其示例。 --- #### 方法一:使用 `PIVOT` 子句 Oracle 11g 及以后版本提供了内置的 `PIVOT` 功能,可以直接将行数据转化为列展示。 ##### 示例场景: 假设有如下销售数据表 `sales`,其中包含销售人员 (`employee`)、产品类别 (`category`) 和销售额 (`amount`) 的信息: | employee | category | amount | |----------|----------|--------| | Alice | A | 100 | | Bob | B | 200 | | Alice | C | 300 | 我们需要生成一张交叉表,按员工分类汇总各产品的销售额。 ##### SQL 实现: ```sql SELECT * FROM sales PIVOT ( SUM(amount) FOR category IN ('A' AS Category_A, 'B' AS Category_B, 'C' AS Category_C) ); ``` ##### 结果: | employee | Category_A | Category_B | Category_C | |----------|------------|------------|------------| | Alice | 100 | NULL | 300 | | Bob | NULL | 200 | NULL | 解释:`SUM(amount)` 是聚合函数,表示对每种类别的金额求和;`FOR category IN (...)` 定义了要转换的具体列名[^4]。 --- #### 方法二:手动构建 PIVOT 查询 如果使用的 Oracle 版本低于 11g,或者需要更高的灵活性,可以手动编写交叉表查询。 ##### 示例场景: 继续沿用上面的 `sales` 表数据。 ##### SQL 实现: ```sql SELECT employee, MAX(CASE WHEN category = 'A' THEN amount ELSE NULL END) AS Category_A, MAX(CASE WHEN category = 'B' THEN amount ELSE NULL END) AS Category_B, MAX(CASE WHEN category = 'C' THEN amount ELSE NULL END) AS Category_C FROM sales GROUP BY employee; ``` ##### 结果: 与前一种方法的结果一致。 解释:通过 `CASE` 语句判断每一行所属的类别,并将其值分配到对应的列中。最后使用 `MAX()` 聚合函数消除重复项[^5]。 --- #### 方法三:反向操作——UNPIVOT 有时也需要将列数据重新转回行为数据,这称为 **UNPIVOT** 操作。 ##### 示例场景: 给定以下交叉表数据: | employee | Category_A | Category_B | Category_C | |----------|------------|------------|------------| | Alice | 100 | NULL | 300 | | Bob | NULL | 200 | NULL | 希望将其还原为原始的行数据格式。 ##### SQL 实现: ```sql SELECT employee, category, amount FROM ( SELECT employee, Category_A, Category_B, Category_C FROM unpivoted_sales ) UNPIVOT ( amount FOR category IN (Category_A AS 'A', Category_B AS 'B', Category_C AS 'C') ); ``` ##### 结果: | employee | category | amount | |----------|----------|--------| | Alice | A | 100 | | Bob | B | 200 | | Alice | C | 300 | 解释:`UNPIVOT` 将多个列的数据重新整理为单一列的形式[^4]。 --- ### 总结 - 如果使用的是 Oracle 11g 或更高版本,推荐优先使用 `PIVOT` 和 `UNPIVOT` 子句,因其语法简洁且易于维护。 - 对于低版本数据库或特殊需求,可以通过组合 `CASE` 和聚合函数手动实现类似的交叉表效果。 ---
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值