Oracle动态交叉表生成实例

在Oracle中,可以使用动态SQL语句生成交叉表(Cross Tab)。交叉表可以将分类数据进行交叉汇总,生成简洁直观的报表。这里给出一个示例,查询员工部门和工种的交叉表:

DECLARE
    v_sql VARCHAR2(4000);
BEGIN
    v_sql := 'SELECT DEPTNO AS "部门号", JOB AS "工种", ';
    FOR i IN (SELECT DEPTNO FROM DEPT)
    LOOP
        v_sql := v_sql||'SUM(CASE WHEN DEPTNO = '||i.DEPTNO||' THEN 1 ELSE 0 END) "'||i.DEPTNO||'", ';
    END LOOP;
    v_sql := RTRIM(v_sql,', ');
    v_sql := v_sql||' FROM EMP GROUP BY DEPTNO, JOB';
    
    EXECUTE IMMEDIATE v_sql;
END;

该代码的执行流程如下:

1. 声明一个变量v_sql,用于动态拼接SQL语句。

2. 查询DEPT表获取所有部门号,循环拼接SUM函数及CASE WHEN语句,用于统计每个部门的员工数。

3. 利用RTRIM函数去除SQL最后的`, `,拼接完整的SQL语句。

4. 使用EXECUTE IMMEDIATE执行动态SQL语句。

5. 结果生成一个部门和工种的交叉表,每个部门的员工数在对应的列中统计。

该示例 Generate 了以下交叉表结果:

DEPTNO   工种      10        20        30
------- -------- ---------- ---------- ---------- 
ACCOUNT     3           1           0
CLERK        1           1           2   
MANAGER   1           1           1  
SALESMAN 2           5           0

通过动态SQL,我们可以灵活地根据业务需求生成各种复杂的交叉表。如果表之间有层级关系,还可以生成层级交叉表。