存在以下两个表
部门表:
部门ID 部门名称
1 部门1
----------------------------------
员工表:
员工Id 员工名 性别 所属部门
1 员工1 女 1
2 员工2 女 1
3 员工3 女 1
4 员工4 女 1
5 员工5 男 1
6 员工6 女 1
7 员工7 男 1
8 员工8 男 1
9 员工9 女 1
10 员工10 男 1
11 员工11 女 1
12 员工12 女 1
13 员工13 男 1
14 员工14 男 1
15 员工15 男 1
16 员工16 女 1
17 员工17 男 1
18 员工18 男 1
19 员工19 男 1
20 员工20 男 1
-------------------------------
通过sql查询后结果显示如下:
部门名 员工1 员工2 员工3 员工4 员工5 员工6 员工7 员工8 员工9 员工10 员工11 员工12 员工13 员工14 员工15 员工16 员工17 员工18 员工19 员工20
部门1 女 女 女 女 男 女 男 男 女 男 女 女 男 男 男 女 男 男 男 男
其中员工数是会变动的,所以横向的列数是不定的,所以希望可以动态显示列。
create or replace procedure procDepart is
sqlstr Clob := '';
sqlExtrData Clob :='';
tblCount number(10):=0;
begin
select count(*) into tblCount from user_tables utb where utb.table_name='temp_depart_report';
if tblCount>0 then
dbms_output.put_line('drop table temp_depart_report');
execute immediate 'drop table temp_depart_report';
end if;
sqlExtrData:='create table temp_depart_report as (
select * from
(select tw.create_date,
tc.claim_no,
tt.description as taskName,
tc.case_id,
tb.object_id,
tr.flaged_counts,
tri.flag,
tin.indicator_description
from depart de,
emp em,
where de.depart_id = em.depart_id
)tbl
)';
dbms_output.put_line('sqlExtrData:' ||sqlExtrData);
execute immediate sqlExtrData;
select WM_CONCAT(''''||sex||'''') into sqlstr from (select distinct t.sex from temp_depart_report t) t;
sqlstr:='SELECT *
FROM temp_depart_report
PIVOT (
MAX(sex)
FOR emp_no
IN ('||sqlstr||')
) ORDER BY case_id asc';
execute immediate sqlstr;
dbms_output.put_line('sqlstr:'|| sqlstr);
sqlstr := 'CREATE OR REPLACE VIEW v_depart_report AS '|| sqlstr;
--sqlRet :='select * from tmp_result';
execute immediate sqlstr;
end procDepart;
特别需要注意的是:在pivot for子句中for 是需要按照哪一列转换成行,in子句是哪些需要转换成列,max(聚合函数子句)指的是行转换成列后的值。
其中pivot,会隐式group by,如果在pivot子句前的select 语句查询出来的结果所包含的记录并不能group by时,将会显示多行,行转列后的值分别显示在不同行。其中隐式group by时,group by的对象是除pivot聚合函数用到的字段与pivot for子句用到的字段外的所有字段进行group by,然后将group by 的相同的信息显示在一行上,将行转换成列的值拼接在group by对象的右方显示。