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