select 部门编号,员工等级号,count(1) from 部门表 A ,员工表 B where 部门员工号 = 员工表员工号 group by 部门编号,员工等级号试一下
--类似例子 select job "职位",sum(f1) "部门1",sum(f2) "部门2",sum(f3) "部门3" from (select job, case when deptno = 10 then 1 else 0 end f1, case when deptno = 20 then 1 else 0 end f2, case when deptno = 30 then 1 else 0 end f3 from (select d.deptno, d.dname,job from emp e,dept d where e.deptno = d.deptno) ) group by job;
with dept as ( select '部门A' dname, 100 as empno from dual union all select '部门A' dname, 101 as empno from dual union all select '部门A' dname, 102 as empno from dual union all select '部门A' dname, 103 as empno from dual union all select '部门A' dname, 104 as empno from dual union all select '部门A' dname, 105 as empno from dual union all select '部门A' dname, 106 as empno from dual union all select '部门A' dname, 107 as empno from dual union all select '部门B' dname, 200 as empno from dual union all select '部门B' dname, 201 as empno from dual union all select '部门B' dname, 202 as empno from dual union all select '部门B' dname, 203 as empno from dual union all select '部门B' dname, 204 as empno from dual union all select '部门B' dname, 205 as empno from dual union all select '部门B' dname, 206 as empno from dual union all select '部门B' dname, 207 as empno from dual ), emp as ( select 100 as empno, 1 as lvl from dual union all select 101 as empno, 1 as lvl from dual union all select 102 as empno, 2 as lvl from dual union all select 103 as empno, 2 as lvl from dual union all select 104 as empno, 2 as lvl from dual union all select 105 as empno, 3 as lvl from dual union all select 106 as empno, 3 as lvl from dual union all select 107 as empno, 3 as lvl from dual union all select 200 as empno, 1 as lvl from dual union all select 201 as empno, 2 as lvl from dual union all select 202 as empno, 2 as lvl from dual union all select 203 as empno, 2 as lvl from dual union all select 204 as empno, 2 as lvl from dual union all select 205 as empno, 3 as lvl from dual union all select 206 as empno, 3 as lvl from dual union all select 207 as empno, 3 as lvl from dual ) select lvl, sum(case when dname = '部门A' then 1 else 0 end) as "部门A", sum(case when dname = '部门B' then 1 else 0 end) as "部门B" from dept a, emp b where a.empno = b.empno group by lvl; LVL 部门A 部门B ---------- ---------- ---------- 1 2 1 2 3 4 3 3 3
with dept as ( select '部门A' dname, 100 as empno from dual union all select '部门A' dname, 101 as empno from dual union all select '部门A' dname, 102 as empno from dual union all select '部门A' dname, 103 as empno from dual union all select '部门A' dname, 104 as empno from dual union all select '部门A' dname, 105 as empno from dual union all select '部门A' dname, 106 as empno from dual union all select '部门A' dname, 107 as empno from dual union all select '部门B' dname, 200 as empno from dual union all select '部门B' dname, 201 as empno from dual union all select '部门B' dname, 202 as empno from dual union all select '部门B' dname, 203 as empno from dual union all select '部门B' dname, 204 as empno from dual union all select '部门B' dname, 205 as empno from dual union all select '部门B' dname, 206 as empno from dual union all select '部门B' dname, 207 as empno from dual ), emp as ( select 100 as empno, 1 as lvl from dual union all select 101 as empno, 1 as lvl from dual union all select 102 as empno, 2 as lvl from dual union all select 103 as empno, 2 as lvl from dual union all select 104 as empno, 2 as lvl from dual union all select 105 as empno, 3 as lvl from dual union all select 106 as empno, 3 as lvl from dual union all select 107 as empno, 3 as lvl from dual union all select 200 as empno, 4 as lvl from dual union all select 201 as empno, 2 as lvl from dual union all select 202 as empno, 2 as lvl from dual union all select 203 as empno, 2 as lvl from dual union all select 204 as empno, 2 as lvl from dual union all select 205 as empno, 3 as lvl from dual union all select 206 as empno, 3 as lvl from dual union all select 207 as empno, 3 as lvl from dual ) select lvl, sum(case when dname = '部门A' then 1 else 0 end) as "部门A", sum(case when dname = '部门B' then 1 else 0 end) as "部门B" from dept a right join emp b on a.empno = b.empno group by lvl order by lvl; LVL 部门A 部门B ---------- ---------- ---------- 1 2 0 2 3 4 3 3 3 4 0 1 稍微修改下
SELECT 等级,SUM(员工) over (PARTITION BY 部门) FROM 部门表,员工表 WHERE 部门表.员工 = 员工表.员工
部门若有N个,就要动态生成,那就比较麻烦了,需要另外写SP来实现了
行转列CREATE TABLE t_row_col AS SELECT id, 'c1' cn, c1 cv FROM t_col_row UNION ALL SELECT id, 'c2' cn, c2 cv FROM t_col_row UNION ALL SELECT id, 'c3' cn, c3 cv FROM t_col_row;SELECT * FROM t_row_col ORDER BY 1,2;1)AGGREGATE FUNCTION 适用范围:8i,9i,10g及以后版本 SELECT id, MAX(decode(cn, 'c1', cv, NULL)) AS c1, MAX(decode(cn, 'c2', cv, NULL)) AS c2, MAX(decode(cn, 'c3', cv, NULL)) AS c3 FROM t_row_col GROUP BY id ORDER BY 1;MAX聚集函数也可以用sum、min、avg等其他聚集函数替代。被指定的转置列只能有一列,但固定的列可以有多列,请看下面的例子:SELECT mgr, deptno, empno, ename FROM emp ORDER BY 1, 2;SELECT mgr, deptno, MAX(decode(empno, '7788', ename, NULL)) "7788", MAX(decode(empno, '7902', ename, NULL)) "7902", MAX(decode(empno, '7844', ename, NULL)) "7844", MAX(decode(empno, '7521', ename, NULL)) "7521", MAX(decode(empno, '7900', ename, NULL)) "7900", MAX(decode(empno, '7499', ename, NULL)) "7499", MAX(decode(empno, '7654', ename, NULL)) "7654" FROM emp WHERE mgr IN (7566, 7698) AND deptno IN (20, 30) GROUP BY mgr, deptno ORDER BY 1, 2;这里转置列为empno,固定列为mgr,deptno。还有一种行转列的方式,就是相同组中的行值变为单个列值,但转置的行值不变为列名:ID CN_1 CV_1 CN_2 CV_2 CN_3 CV_3 1 c1 v11 c2 v21 c3 v31 2 c1 v12 c2 v22 c3 3 c1 v13 c2 c3 v33 4 c1 c2 v24 c3 v34 5 c1 v15 c2 c3 6 c1 c2 c3 v35 7 c1 c2 c3这种情况可以用分析函数实现:SELECT id, MAX(decode(rn, 1, cn, NULL)) cn_1, MAX(decode(rn, 1, cv, NULL)) cv_1, MAX(decode(rn, 2, cn, NULL)) cn_2, MAX(decode(rn, 2, cv, NULL)) cv_2, MAX(decode(rn, 3, cn, NULL)) cn_3, MAX(decode(rn, 3, cv, NULL)) cv_3 FROM (SELECT id, cn, cv, row_number() over(PARTITION BY id ORDER BY cn, cv) rn FROM t_row_col) GROUP BY ID;2)PL/SQL 适用范围:8i,9i,10g及以后版本 这种对于行值不固定的情况可以使用。 下面是我写的一个包,包中 p_rows_column_real用于前述的第一种不限定列的转换; p_rows_column用于前述的第二种不限定列的转换。CREATE OR REPLACE PACKAGE pkg_dynamic_rows_column AS TYPE refc IS REF CURSOR; PROCEDURE p_print_sql(p_txt VARCHAR2); FUNCTION f_split_str(p_str VARCHAR2, p_division VARCHAR2, p_seq INT) RETURN VARCHAR2; PROCEDURE p_rows_column(p_table IN VARCHAR2, p_keep_cols IN VARCHAR2, p_pivot_cols IN VARCHAR2, p_where IN VARCHAR2 DEFAULT NULL, p_refc IN OUT refc); PROCEDURE p_rows_column_real(p_table IN VARCHAR2, p_keep_cols IN VARCHAR2, p_pivot_col IN VARCHAR2, p_pivot_val IN VARCHAR2, p_where IN VARCHAR2 DEFAULT NULL, p_refc IN OUT refc); END; / CREATE OR REPLACE PACKAGE BODY pkg_dynamic_rows_column AS PROCEDURE p_print_sql(p_txt VARCHAR2) IS v_len INT; BEGIN v_len := length(p_txt); FOR i IN 1 .. v_len / 250 + 1 LOOP dbms_output.put_line(substrb(p_txt, (i - 1) * 250 + 1, 250)); END LOOP; END; FUNCTION f_split_str(p_str VARCHAR2, p_division VARCHAR2, p_seq INT) RETURN VARCHAR2 IS v_first INT; v_last INT; BEGIN IF p_seq < 1 THEN RETURN NULL; END IF; IF p_seq = 1 THEN IF instr(p_str, p_division, 1, p_seq) = 0 THEN RETURN p_str; ELSE RETURN substr(p_str, 1, instr(p_str, p_division, 1) - 1); END IF; ELSE v_first := instr(p_str, p_division, 1, p_seq - 1); v_last := instr(p_str, p_division, 1, p_seq); IF (v_last = 0) THEN IF (v_first > 0) THEN RETURN substr(p_str, v_first + 1); ELSE RETURN NULL; END IF; ELSE RETURN substr(p_str, v_first + 1, v_last - v_first - 1); END IF; END IF; END f_split_str; PROCEDURE p_rows_column(p_table IN VARCHAR2, p_keep_cols IN VARCHAR2, p_pivot_cols IN VARCHAR2, p_where IN VARCHAR2 DEFAULT NULL, p_refc IN OUT refc) IS v_sql VARCHAR2(4000); TYPE v_keep_ind_by IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER; v_keep v_keep_ind_by;
TYPE v_pivot_ind_by IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER; v_pivot v_pivot_ind_by;
v_keep_cnt INT; v_pivot_cnt INT; v_max_cols INT; v_partition VARCHAR2(4000); v_partition1 VARCHAR2(4000); v_partition2 VARCHAR2(4000); BEGIN v_keep_cnt := length(p_keep_cols) - length(REPLACE(p_keep_cols, ',')) + 1; v_pivot_cnt := length(p_pivot_cols) - length(REPLACE(p_pivot_cols, ',')) + 1; FOR i IN 1 .. v_keep_cnt LOOP v_keep(i) := f_split_str(p_keep_cols, ',', i); END LOOP; FOR j IN 1 .. v_pivot_cnt LOOP v_pivot(j) := f_split_str(p_pivot_cols, ',', j); END LOOP; v_sql := 'select max(count(*)) from ' || p_table || ' group by '; FOR i IN 1 .. v_keep.LAST LOOP v_sql := v_sql || v_keep(i) || ','; END LOOP; v_sql := rtrim(v_sql, ','); EXECUTE IMMEDIATE v_sql INTO v_max_cols; v_partition := 'select '; FOR x IN 1 .. v_keep.COUNT LOOP v_partition1 := v_partition1 || v_keep(x) || ','; END LOOP; FOR y IN 1 .. v_pivot.COUNT LOOP v_partition2 := v_partition2 || v_pivot(y) || ','; END LOOP; v_partition1 := rtrim(v_partition1, ','); v_partition2 := rtrim(v_partition2, ','); v_partition := v_partition || v_partition1 || ',' || v_partition2 || ', row_number() over (partition by ' || v_partition1 || ' order by ' || v_partition2 || ') rn from ' || p_table; v_partition := rtrim(v_partition, ','); v_sql := 'select '; FOR i IN 1 .. v_keep.COUNT LOOP v_sql := v_sql || v_keep(i) || ','; END LOOP; FOR i IN 1 .. v_max_cols LOOP FOR j IN 1 .. v_pivot.COUNT LOOP v_sql := v_sql || ' max(decode(rn,' || i || ',' || v_pivot(j) || ',null))' || v_pivot(j) || '_' || i || ','; END LOOP; END LOOP; IF p_where IS NOT NULL THEN v_sql := rtrim(v_sql, ',') || ' from (' || v_partition || ' ' || p_where || ') group by '; ELSE v_sql := rtrim(v_sql, ',') || ' from (' || v_partition || ') group by '; END IF; FOR i IN 1 .. v_keep.COUNT LOOP v_sql := v_sql || v_keep(i) || ','; END LOOP; v_sql := rtrim(v_sql, ','); p_print_sql(v_sql); OPEN p_refc FOR v_sql; EXCEPTION WHEN OTHERS THEN OPEN p_refc FOR SELECT 'x' FROM dual WHERE 0 = 1; END; PROCEDURE p_rows_column_real(p_table IN VARCHAR2, p_keep_cols IN VARCHAR2, p_pivot_col IN VARCHAR2, p_pivot_val IN VARCHAR2, p_where IN VARCHAR2 DEFAULT NULL, p_refc IN OUT refc) IS v_sql VARCHAR2(4000); TYPE v_keep_ind_by IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER; v_keep v_keep_ind_by; TYPE v_pivot_ind_by IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER; v_pivot v_pivot_ind_by; v_keep_cnt INT; v_group_by VARCHAR2(2000); BEGIN v_keep_cnt := length(p_keep_cols) - length(REPLACE(p_keep_cols, ',')) + 1; FOR i IN 1 .. v_keep_cnt LOOP v_keep(i) := f_split_str(p_keep_cols, ',', i); END LOOP; v_sql := 'select ' || 'cast(' || p_pivot_col || ' as varchar2(200)) as ' || p_pivot_col || ' from ' || p_table || ' group by ' || p_pivot_col; EXECUTE IMMEDIATE v_sql BULK COLLECT INTO v_pivot; FOR i IN 1 .. v_keep.COUNT LOOP v_group_by := v_group_by || v_keep(i) || ','; END LOOP; v_group_by := rtrim(v_group_by, ','); v_sql := 'select ' || v_group_by || ',';
FOR x IN 1 .. v_pivot.COUNT LOOP v_sql := v_sql || ' max(decode(' || p_pivot_col || ',' || chr(39) || v_pivot(x) || chr(39) || ',' || p_pivot_val || ',null)) as "' || v_pivot(x) || '",'; END LOOP; v_sql := rtrim(v_sql, ','); IF p_where IS NOT NULL THEN v_sql := v_sql || ' from ' || p_table || p_where || ' group by ' || v_group_by; ELSE v_sql := v_sql || ' from ' || p_table || ' group by ' || v_group_by; END IF; p_print_sql(v_sql); OPEN p_refc FOR v_sql; EXCEPTION WHEN OTHERS THEN OPEN p_refc FOR SELECT 'x' FROM dual WHERE 0 = 1; END;END;
--类似例子
select job "职位",sum(f1) "部门1",sum(f2) "部门2",sum(f3) "部门3"
from
(select job,
case when deptno = 10 then 1 else 0 end f1,
case when deptno = 20 then 1 else 0 end f2,
case when deptno = 30 then 1 else 0 end f3
from
(select d.deptno, d.dname,job from emp e,dept d where e.deptno = d.deptno)
)
group by job;
with dept as
(
select '部门A' dname, 100 as empno from dual
union all
select '部门A' dname, 101 as empno from dual
union all
select '部门A' dname, 102 as empno from dual
union all
select '部门A' dname, 103 as empno from dual
union all
select '部门A' dname, 104 as empno from dual
union all
select '部门A' dname, 105 as empno from dual
union all
select '部门A' dname, 106 as empno from dual
union all
select '部门A' dname, 107 as empno from dual
union all
select '部门B' dname, 200 as empno from dual
union all
select '部门B' dname, 201 as empno from dual
union all
select '部门B' dname, 202 as empno from dual
union all
select '部门B' dname, 203 as empno from dual
union all
select '部门B' dname, 204 as empno from dual
union all
select '部门B' dname, 205 as empno from dual
union all
select '部门B' dname, 206 as empno from dual
union all
select '部门B' dname, 207 as empno from dual
),
emp as
(
select 100 as empno, 1 as lvl from dual
union all
select 101 as empno, 1 as lvl from dual
union all
select 102 as empno, 2 as lvl from dual
union all
select 103 as empno, 2 as lvl from dual
union all
select 104 as empno, 2 as lvl from dual
union all
select 105 as empno, 3 as lvl from dual
union all
select 106 as empno, 3 as lvl from dual
union all
select 107 as empno, 3 as lvl from dual
union all
select 200 as empno, 1 as lvl from dual
union all
select 201 as empno, 2 as lvl from dual
union all
select 202 as empno, 2 as lvl from dual
union all
select 203 as empno, 2 as lvl from dual
union all
select 204 as empno, 2 as lvl from dual
union all
select 205 as empno, 3 as lvl from dual
union all
select 206 as empno, 3 as lvl from dual
union all
select 207 as empno, 3 as lvl from dual
)
select lvl,
sum(case when dname = '部门A' then 1 else 0 end) as "部门A",
sum(case when dname = '部门B' then 1 else 0 end) as "部门B"
from dept a, emp b
where a.empno = b.empno
group by lvl;
LVL 部门A 部门B
---------- ---------- ----------
1 2 1
2 3 4
3 3 3
with dept as
(
select '部门A' dname, 100 as empno from dual
union all
select '部门A' dname, 101 as empno from dual
union all
select '部门A' dname, 102 as empno from dual
union all
select '部门A' dname, 103 as empno from dual
union all
select '部门A' dname, 104 as empno from dual
union all
select '部门A' dname, 105 as empno from dual
union all
select '部门A' dname, 106 as empno from dual
union all
select '部门A' dname, 107 as empno from dual
union all
select '部门B' dname, 200 as empno from dual
union all
select '部门B' dname, 201 as empno from dual
union all
select '部门B' dname, 202 as empno from dual
union all
select '部门B' dname, 203 as empno from dual
union all
select '部门B' dname, 204 as empno from dual
union all
select '部门B' dname, 205 as empno from dual
union all
select '部门B' dname, 206 as empno from dual
union all
select '部门B' dname, 207 as empno from dual
),
emp as
(
select 100 as empno, 1 as lvl from dual
union all
select 101 as empno, 1 as lvl from dual
union all
select 102 as empno, 2 as lvl from dual
union all
select 103 as empno, 2 as lvl from dual
union all
select 104 as empno, 2 as lvl from dual
union all
select 105 as empno, 3 as lvl from dual
union all
select 106 as empno, 3 as lvl from dual
union all
select 107 as empno, 3 as lvl from dual
union all
select 200 as empno, 4 as lvl from dual
union all
select 201 as empno, 2 as lvl from dual
union all
select 202 as empno, 2 as lvl from dual
union all
select 203 as empno, 2 as lvl from dual
union all
select 204 as empno, 2 as lvl from dual
union all
select 205 as empno, 3 as lvl from dual
union all
select 206 as empno, 3 as lvl from dual
union all
select 207 as empno, 3 as lvl from dual
)
select lvl,
sum(case when dname = '部门A' then 1 else 0 end) as "部门A",
sum(case when dname = '部门B' then 1 else 0 end) as "部门B"
from dept a right join emp b
on a.empno = b.empno
group by lvl
order by lvl;
LVL 部门A 部门B
---------- ---------- ----------
1 2 0
2 3 4
3 3 3
4 0 1
稍微修改下
FROM 部门表,员工表
WHERE 部门表.员工 = 员工表.员工
SELECT id, 'c1' cn, c1 cv
FROM t_col_row
UNION ALL
SELECT id, 'c2' cn, c2 cv
FROM t_col_row
UNION ALL
SELECT id, 'c3' cn, c3 cv FROM t_col_row;SELECT * FROM t_row_col ORDER BY 1,2;1)AGGREGATE FUNCTION
适用范围:8i,9i,10g及以后版本
SELECT id,
MAX(decode(cn, 'c1', cv, NULL)) AS c1,
MAX(decode(cn, 'c2', cv, NULL)) AS c2,
MAX(decode(cn, 'c3', cv, NULL)) AS c3
FROM t_row_col
GROUP BY id
ORDER BY 1;MAX聚集函数也可以用sum、min、avg等其他聚集函数替代。被指定的转置列只能有一列,但固定的列可以有多列,请看下面的例子:SELECT mgr, deptno, empno, ename FROM emp ORDER BY 1, 2;SELECT mgr,
deptno,
MAX(decode(empno, '7788', ename, NULL)) "7788",
MAX(decode(empno, '7902', ename, NULL)) "7902",
MAX(decode(empno, '7844', ename, NULL)) "7844",
MAX(decode(empno, '7521', ename, NULL)) "7521",
MAX(decode(empno, '7900', ename, NULL)) "7900",
MAX(decode(empno, '7499', ename, NULL)) "7499",
MAX(decode(empno, '7654', ename, NULL)) "7654"
FROM emp
WHERE mgr IN (7566, 7698)
AND deptno IN (20, 30)
GROUP BY mgr, deptno
ORDER BY 1, 2;这里转置列为empno,固定列为mgr,deptno。还有一种行转列的方式,就是相同组中的行值变为单个列值,但转置的行值不变为列名:ID CN_1 CV_1 CN_2 CV_2 CN_3 CV_3
1 c1 v11 c2 v21 c3 v31
2 c1 v12 c2 v22 c3
3 c1 v13 c2 c3 v33
4 c1 c2 v24 c3 v34
5 c1 v15 c2 c3
6 c1 c2 c3 v35
7 c1 c2 c3这种情况可以用分析函数实现:SELECT id,
MAX(decode(rn, 1, cn, NULL)) cn_1,
MAX(decode(rn, 1, cv, NULL)) cv_1,
MAX(decode(rn, 2, cn, NULL)) cn_2,
MAX(decode(rn, 2, cv, NULL)) cv_2,
MAX(decode(rn, 3, cn, NULL)) cn_3,
MAX(decode(rn, 3, cv, NULL)) cv_3
FROM (SELECT id,
cn,
cv,
row_number() over(PARTITION BY id ORDER BY cn, cv) rn
FROM t_row_col)
GROUP BY ID;2)PL/SQL
适用范围:8i,9i,10g及以后版本
这种对于行值不固定的情况可以使用。
下面是我写的一个包,包中
p_rows_column_real用于前述的第一种不限定列的转换;
p_rows_column用于前述的第二种不限定列的转换。CREATE OR REPLACE PACKAGE pkg_dynamic_rows_column AS
TYPE refc IS REF CURSOR; PROCEDURE p_print_sql(p_txt VARCHAR2); FUNCTION f_split_str(p_str VARCHAR2, p_division VARCHAR2, p_seq INT)
RETURN VARCHAR2; PROCEDURE p_rows_column(p_table IN VARCHAR2,
p_keep_cols IN VARCHAR2,
p_pivot_cols IN VARCHAR2,
p_where IN VARCHAR2 DEFAULT NULL,
p_refc IN OUT refc); PROCEDURE p_rows_column_real(p_table IN VARCHAR2,
p_keep_cols IN VARCHAR2,
p_pivot_col IN VARCHAR2,
p_pivot_val IN VARCHAR2,
p_where IN VARCHAR2 DEFAULT NULL,
p_refc IN OUT refc);
END;
/
CREATE OR REPLACE PACKAGE BODY pkg_dynamic_rows_column AS PROCEDURE p_print_sql(p_txt VARCHAR2) IS
v_len INT;
BEGIN
v_len := length(p_txt);
FOR i IN 1 .. v_len / 250 + 1 LOOP
dbms_output.put_line(substrb(p_txt, (i - 1) * 250 + 1, 250));
END LOOP;
END; FUNCTION f_split_str(p_str VARCHAR2, p_division VARCHAR2, p_seq INT)
RETURN VARCHAR2 IS
v_first INT;
v_last INT;
BEGIN
IF p_seq < 1 THEN
RETURN NULL;
END IF;
IF p_seq = 1 THEN
IF instr(p_str, p_division, 1, p_seq) = 0 THEN
RETURN p_str;
ELSE
RETURN substr(p_str, 1, instr(p_str, p_division, 1) - 1);
END IF;
ELSE
v_first := instr(p_str, p_division, 1, p_seq - 1);
v_last := instr(p_str, p_division, 1, p_seq);
IF (v_last = 0) THEN
IF (v_first > 0) THEN
RETURN substr(p_str, v_first + 1);
ELSE
RETURN NULL;
END IF;
ELSE
RETURN substr(p_str, v_first + 1, v_last - v_first - 1);
END IF;
END IF;
END f_split_str; PROCEDURE p_rows_column(p_table IN VARCHAR2,
p_keep_cols IN VARCHAR2,
p_pivot_cols IN VARCHAR2,
p_where IN VARCHAR2 DEFAULT NULL,
p_refc IN OUT refc) IS
v_sql VARCHAR2(4000);
TYPE v_keep_ind_by IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
v_keep v_keep_ind_by;
TYPE v_pivot_ind_by IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
v_pivot v_pivot_ind_by;
v_keep_cnt INT;
v_pivot_cnt INT;
v_max_cols INT;
v_partition VARCHAR2(4000);
v_partition1 VARCHAR2(4000);
v_partition2 VARCHAR2(4000);
BEGIN
v_keep_cnt := length(p_keep_cols) - length(REPLACE(p_keep_cols, ',')) + 1;
v_pivot_cnt := length(p_pivot_cols) -
length(REPLACE(p_pivot_cols, ',')) + 1;
FOR i IN 1 .. v_keep_cnt LOOP
v_keep(i) := f_split_str(p_keep_cols, ',', i);
END LOOP;
FOR j IN 1 .. v_pivot_cnt LOOP
v_pivot(j) := f_split_str(p_pivot_cols, ',', j);
END LOOP;
v_sql := 'select max(count(*)) from ' || p_table || ' group by ';
FOR i IN 1 .. v_keep.LAST LOOP
v_sql := v_sql || v_keep(i) || ',';
END LOOP;
v_sql := rtrim(v_sql, ',');
EXECUTE IMMEDIATE v_sql
INTO v_max_cols;
v_partition := 'select ';
FOR x IN 1 .. v_keep.COUNT LOOP
v_partition1 := v_partition1 || v_keep(x) || ',';
END LOOP;
FOR y IN 1 .. v_pivot.COUNT LOOP
v_partition2 := v_partition2 || v_pivot(y) || ',';
END LOOP;
v_partition1 := rtrim(v_partition1, ',');
v_partition2 := rtrim(v_partition2, ',');
v_partition := v_partition || v_partition1 || ',' || v_partition2 ||
', row_number() over (partition by ' || v_partition1 ||
' order by ' || v_partition2 || ') rn from ' || p_table;
v_partition := rtrim(v_partition, ',');
v_sql := 'select ';
FOR i IN 1 .. v_keep.COUNT LOOP
v_sql := v_sql || v_keep(i) || ',';
END LOOP;
FOR i IN 1 .. v_max_cols LOOP
FOR j IN 1 .. v_pivot.COUNT LOOP
v_sql := v_sql || ' max(decode(rn,' || i || ',' || v_pivot(j) ||
',null))' || v_pivot(j) || '_' || i || ',';
END LOOP;
END LOOP;
IF p_where IS NOT NULL THEN
v_sql := rtrim(v_sql, ',') || ' from (' || v_partition || ' ' ||
p_where || ') group by ';
ELSE
v_sql := rtrim(v_sql, ',') || ' from (' || v_partition ||
') group by ';
END IF;
FOR i IN 1 .. v_keep.COUNT LOOP
v_sql := v_sql || v_keep(i) || ',';
END LOOP;
v_sql := rtrim(v_sql, ',');
p_print_sql(v_sql);
OPEN p_refc FOR v_sql;
EXCEPTION
WHEN OTHERS THEN
OPEN p_refc FOR
SELECT 'x' FROM dual WHERE 0 = 1;
END; PROCEDURE p_rows_column_real(p_table IN VARCHAR2,
p_keep_cols IN VARCHAR2,
p_pivot_col IN VARCHAR2,
p_pivot_val IN VARCHAR2,
p_where IN VARCHAR2 DEFAULT NULL,
p_refc IN OUT refc) IS
v_sql VARCHAR2(4000);
TYPE v_keep_ind_by IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
v_keep v_keep_ind_by;
TYPE v_pivot_ind_by IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
v_pivot v_pivot_ind_by;
v_keep_cnt INT;
v_group_by VARCHAR2(2000);
BEGIN
v_keep_cnt := length(p_keep_cols) - length(REPLACE(p_keep_cols, ',')) + 1;
FOR i IN 1 .. v_keep_cnt LOOP
v_keep(i) := f_split_str(p_keep_cols, ',', i);
END LOOP;
v_sql := 'select ' || 'cast(' || p_pivot_col ||
' as varchar2(200)) as ' || p_pivot_col || ' from ' || p_table ||
' group by ' || p_pivot_col;
EXECUTE IMMEDIATE v_sql BULK COLLECT
INTO v_pivot;
FOR i IN 1 .. v_keep.COUNT LOOP
v_group_by := v_group_by || v_keep(i) || ',';
END LOOP;
v_group_by := rtrim(v_group_by, ',');
v_sql := 'select ' || v_group_by || ',';
FOR x IN 1 .. v_pivot.COUNT LOOP
v_sql := v_sql || ' max(decode(' || p_pivot_col || ',' || chr(39) ||
v_pivot(x) || chr(39) || ',' || p_pivot_val ||
',null)) as "' || v_pivot(x) || '",';
END LOOP;
v_sql := rtrim(v_sql, ',');
IF p_where IS NOT NULL THEN
v_sql := v_sql || ' from ' || p_table || p_where || ' group by ' ||
v_group_by;
ELSE
v_sql := v_sql || ' from ' || p_table || ' group by ' || v_group_by;
END IF;
p_print_sql(v_sql);
OPEN p_refc FOR v_sql;
EXCEPTION
WHEN OTHERS THEN
OPEN p_refc FOR
SELECT 'x' FROM dual WHERE 0 = 1;
END;END;