Oracle典型行列转换的几种实现方法
原文地址：http://l6259710.iteye.com/blog/1271920
假如有如下表，其中各个i值对应的行数是不定的
- SQL> select * from t;
I A D
---------- ---------- -------------------
1 b 2008-03-27 10:55:42
1 a 2008-03-27 10:55:46
1 d 2008-03-27 10:55:30
2 z 2008-03-27 10:55:55
2 t 2008-03-27 10:55:59
要获得如下结果，注意字符串需要按照D列的时间排序：
1 d,b,a
2 z,t
这是一个比较典型的行列转换，有好几种实现方法
1.自定义函数实现
Sql代码
- create or replace function my_concat(n number)
- return varchar2
- is
- type typ_cursor is ref cursor;
- v_cursor typ_cursor;
- v_temp varchar2(10);
- v_result varchar2(4000):= '';
- v_sql varchar2(200);
- begin
- v_sql := 'select a from t where i=' || n ||' order by d';
- open v_cursor for v_sql;
- loop
- fetch v_cursor into v_temp;
- exit when v_cursor%notfound;
- v_result := v_result ||',' || v_temp;
- end loop;
- return substr(v_result,2);
- end;
- SQL> select i,my_concat(i) from t group by i;
I MY_CONCAT(I)
---------- --------------------
1 d,b,a
2 z,t
虽然这种方式可以实现需求，但是如果表t的数据量很大，i的值又很多的情况下，因为针对每个i值都要执行一句select，扫描和排序的次数和i的值成正比，性能会非常差。
2.使用sys_connect_by_path
从执行计划上来看，这种方式只需要扫描两次表，比自定义函数的方法，效率要高很多，尤其是表中数据量较大的时候：
3.使用wmsys.wm_concat
这个函数也可以实现类似的行列转换需求，但是似乎没有办法做到直接根据另外一列排序，所以需要先通过子查询或者临时表排好序
- SQL> select i,wmsys.wm_concat(a) from t group by i;
I WMSYS.WM_CONCAT(A)
---------- --------------------
1 b,a,d
2 z,t
- SQL> select i,wmsys.wm_concat(a) from (select * from t order by i,d) group by i;
I WMSYS.WM_CONCAT(A)
---------- --------------------
1 d,b,a
2 z,t
执行计划上看，只需要做一次表扫描就可以了，但是这个函数是加密过的，执行计划并不能显示函数内部的操作。
不知道大家还有没有更加高效的实现方式，欢迎指教^_^
其他一些方法，可以参考：
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2196162600402
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:15637744429336
------------------------------------------------------------------------------------------------
- select t.rank, t.Name from t_menu_item t;
10 CLARK
10 KING
10 MILLER
20 ADAMS
20 FORD
20 JONES
20 SCOTT
20 SMITH
30 ALLEN
30 BLAKE
30 JAMES
30 MARTIN
30 TURNER
30 WARD
--------------------------------
我们通过 10g 所提供的 WMSYS.WM_CONCAT 函数即可以完成 行转列的效果
- select t.rank, WMSYS.WM_CONCAT(t.Name) TIME From t_menu_item t GROUP BY t.rank;
DEPTNO ENAME
------ ----------
10 CLARK, KING, MILLER
20 ADAMS, FORD, JONES, SCOTT, SMITH
30 ALLEN, BLAKE, JAMES, MARTIN, TURNER, WARD
例子如下：
- SQL> create table idtable (id number,name varchar2(30));
- Table created
- SQL> insert into idtable values(10,'ab');
- 1 row inserted
- SQL> insert into idtable values(10,'bc');
- 1 row inserted
- SQL> insert into idtable values(10,'cd');
- 1 row inserted
- SQL> insert into idtable values(20,'hi');
- 1 row inserted
- SQL> insert into idtable values(20,'ij');
- 1 row inserted
- SQL> insert into idtable values(20,'mn');
- 1 row inserted
- SQL> select * from idtable;
ID NAME
---------- ------------------------------
10 ab
10 bc
10 cd
20 hi
20 ij
20 mn
6 rows selected
- SQL> select id,wmsys.wm_concat(name) name from idtable 2 group by id;
ID NAME
---------- --------------------------------------------------------------------------------
10 ab,bc,cd
20 hi,ij,mn
- SQL> select id,wmsys.wm_concat(name) over (order by id) name from idtable;
ID NAME
---------- --------------------------------------------------------------------------------
10 ab,bc,cd
10 ab,bc,cd
10 ab,bc,cd
20 ab,bc,cd,hi,ij,mn
20 ab,bc,cd,hi,ij,mn
20 ab,bc,cd,hi,ij,mn
6 rows selected
- SQL> select id,wmsys.wm_concat(name) over (order by id,name) name from idtable;
ID NAME
---------- --------------------------------------------------------------------------------
10 ab
10 ab,bc
10 ab,bc,cd
20 ab,bc,cd,hi
20 ab,bc,cd,hi,ij
20 ab,bc,cd,hi,ij,mn
6 rows selected
个人觉得这个用法比较有趣.
- SQL> select id,wmsys.wm_concat(name) over (partition by id) name from idtable;
ID NAME
---------- --------------------------------------------------------------------------------
10 ab,bc,cd
10 ab,bc,cd
10 ab,bc,cd
20 hi,ij,mn
20 hi,ij,mn
20 hi,ij,mn
6 rows selected
- SQL> select id,wmsys.wm_concat(name) over (partition by id,name) name from idtable;
ID NAME
---------- --------------------------------------------------------------------------------
10 ab
10 bc
10 cd
20 hi
20 ij
20 mn
6 rows selected
ps:
wmsys.wm_concat、sys_connect_by_path、自定义行数实现行列转换：
CREATE TABLE tab_name(ID INTEGER NOT NULL PRIMARY KEY,cName VARCHAR2(20));
CREATE TABLE tab_name2(ID INTEGER NOT NULL,pName VARCHAR2(20));
INSERT INTO tab_name(ID,cName) VALUES (1,'百度');
INSERT INTO tab_name(ID,cName) VALUES (2,'Google');
INSERT INTO tab_name(ID,cName) VALUES (3,'网易');
INSERT INTO tab_name2(ID,pName) VALUES (1,'研发部');
INSERT INTO tab_name2(ID,pName) VALUES (1,'市场部');
INSERT INTO tab_name2(ID,pName) VALUES (2,'研发部');
INSERT INTO tab_name2(ID,pName) VALUES (2,'平台架构');
INSERT INTO tab_name2(ID,pName) VALUES (3,'研发部');
COMMIT;
期望结果：
ID cName pName
1 百度 研发部,市场部
2 Google 研发部
3 网易 研发部,平台架构
方法一:使用wmsys.wm_concat()
SELECT t1.ID,t1.cName,wmsys.wm_concat(t2.pName) FROM tab_name t1,tab_name2 t2 WHERE t1.ID=t2.ID GROUP BY t1.cName,t1.id;
方法二:使用sys_connect_by_path
select id, cName, ltrim(max(sys_connect_by_path(pName, ',')), ',') from (select row_number() over(PARTITION by t1.id ORDER by cName) r,t1.*, t2.pName from tab_name t1, tab_name2 t2 where t1.id = t2.id)
start with r=1 CONNECT by prior r =r-1 and prior id = id group by id ,cName order by id;
方法三:使用自定义函数
create or replace function coltorow(midId INT) RETURN VARCHAR2 is
Result VARCHAR2(1000);
begin
FOR cur IN (SELECT pName FROM tab_name2 t2 WHERE midId=t2.id) LOOP
RESULT:=RESULT||cur.pName||',';
END LOOP;
RESULT:=rtrim(RESULT,',');
return(Result);
end coltorow;
SELECT t1.*,coltorow(t1.ID) FROM tab_name t1,tab_name2 t2 WHERE t1.ID=t2.ID GROUP BY t1.ID,t1.cname ORDER BY t1.ID;
浙公网安备 33010602011771号