oracle行转列怎么写,Oracle行转列的写法

Oracle行转列的写法(转贴)：

行转列

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;