oracle列转行方法总结

 

 

方法一：
----------------------------------------------------------------
---Muti-row to line(col2row)
----------------------------------------------------------------
create or replace type str_tab is table of varchar2(20);
/
grant all on str_tab to public;
create public synonym str_tab for str_tab;
create or replace function col2row(pv in str_tab) return varchar2
is
  ls varchar2(4000);
begin
  for i in 1..pv.count loop
    ls := ls || pv(i);
  end loop;
  return ls;
end;
/
grant execute on col2row to public;
create public synonym col2row for col2row;
----------------------------------------------------------------
--multi column,convert one column base on another column, for example
----------------------------------------------------------------
create table t(id number,name varchar2(10));
insert into t values(1,'Joan');
insert into t values(1,'Jack');
insert into t values(1,'Tom');
insert into t values(2,'Rose');
insert into t values(2,'Jenny');
---------------------------------------------------------------
SQL(c3dev)>select * from t;
        ID NAME
---------- ----------
         1 Joan
         1 Jack
         1 Tom
         2 Rose
         2 Jenny
---------------------------
--column to row
---------------------------
SQL(c3dev)>column names format a80;
SQL(c3dev)>set line 120
SQL(c3dev)>select t0.id,
  2     col2row(cast(multiset(select name from t where t.id = t0.id) as str_tab)) names
  3  from (select distinct id from t) t0;
        ID NAMES
---------- --------------------------------------------------------------------------------
         1 JoanJackTom
         2 RoseJenny

----------------------------------------------------------------
--single column,convert multil row to one row, for example
----------------------------------------------------------------
create table t1(name varchar2(20));
insert into t1 values('Chen');
insert into t1 values('Chuan');
insert into t1 values('Zhong');
SQL(c3dev)>select * from t1;
NAME
--------------------
Chen
Chuan
Zhong
---------------------------
--column to row
---------------------------
select col2row(cast(multiset(select name from t1) as str_tab)) names from t1 where rownum=1
SQL(c3dev)>select col2row(cast(multiset(select name from t1) as str_tab)) names from t1 where rownum=1;
NAMES
--------------------------------------------------------------------------------
ChenChuanZhong

--if need to add list separator
SQL(c3dev)>select col2row(cast(multiset(select name||' ' from t1) as str_tab)) names from t1 where rownum=1;
NAMES
--------------------------------------------------------------------------------
Chen  Chuan Zhong
 
方法二：
create table t2(col1 varchar2(10),col2 varchar2(10));
insert into t2 values('001','vl1');
insert into t2 values('001','vl2');
insert into t2 values('001','vl3');
insert into t2 values('002','vl1');
insert into t2 values('002','vl2');
SELECT COL1,LTRIM(MAX(SYS_CONNECT_BY_PATH(COL2,',')),',') COL2
 FROM
 (
 SELECT COL1,COL2,MIN(COL2) OVER(PARTITION BY COL1) COL2_MIN,
 (ROW_NUMBER() OVER(ORDER BY COL1,COL2))+(DENSE_RANK() OVER (ORDER BY COL1)) NUMID
 FROM T2
 )
 START WITH COL2=COL2_MIN CONNECT BY NUMID-1=PRIOR NUMID
 GROUP BY COL1;
COL1       COL2
---------- ----------------------------------------
001        vl1,vl2,vl3
002        vl1,vl2

 
 方法三（需要知道确定有几行）：
 SELECT deptno, dname, emps
  FROM (SELECT d.deptno, d.dname,
               RTRIM
                  (   e.ename
                   || ', '
                   || LEAD (e.ename, 1) OVER (PARTITION BY d.deptno ORDER BY e.ename)
                   || ', '
                   || LEAD (e.ename, 2) OVER (PARTITION BY d.deptno ORDER BY e.ename)
                   || ', '
                   || LEAD (e.ename, 3) OVER (PARTITION BY d.deptno ORDER BY e.ename)
                   || ', '
                   || LEAD (e.ename, 4) OVER (PARTITION BY d.deptno ORDER BY e.ename)
                   || ', '
                   || LEAD (e.ename, 5) OVER (PARTITION BY d.deptno ORDER BY e.ename),
                   ', '
                  ) emps,
               ROW_NUMBER () OVER (PARTITION BY d.deptno ORDER BY e.ename) x
          FROM emp e, dept d
         WHERE d.deptno = e.deptno)
 WHERE x = 1
/
    DEPTNO DNAME          EMPS
---------- -------------- ----------------------------------------------------------------------
        10 ACCOUNTING     CLARK, KING, MILLER
        20 RESEARCH       ADAMS, FORD, JONES, SCOTT, SMITH
        30 SALES          ALLEN, BLAKE, JAMES, MARTIN, TURNER, WARD