你的分享就是我们的动力 ---﹥

oracle WM_CONCAT行转列

时间:2013-08-05 17:20来源:www.chengxuyuans.com 点击:

?

? Sql代码??收藏代码
  1. 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 函数即可以完成 行转列的效果

???

转:http://l6259710.iteye.com/blog/1271915

Sql代码??收藏代码
  1. 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代码??收藏代码
  1. SQL>?create?table?idtable?(id?number,name?varchar2(30));??
  2. ??
  3. Table?created??
  4. ??
  5. SQL>?insert?into?idtable?values(10,'ab');??
  6. ??
  7. 1?row?inserted??
  8. ??
  9. SQL>?insert?into?idtable?values(10,'bc');??
  10. ??
  11. 1?row?inserted??
  12. ??
  13. SQL>?insert?into?idtable?values(10,'cd');??
  14. ??
  15. 1?row?inserted??
  16. ??
  17. SQL>?insert?into?idtable?values(20,'hi');??
  18. ??
  19. 1?row?inserted??
  20. ??
  21. SQL>?insert?into?idtable?values(20,'ij');??
  22. ??
  23. 1?row?inserted??
  24. SQL>?insert?into?idtable?values(20,'mn');??
  25. ??
  26. 1?row?inserted??
  27. ??
  28. SQL>?select?*?from?idtable;??

?

??????? ID NAME
---------- ------------------------------
??????? 10 ab
??????? 10 bc
??????? 10 cd
??????? 20 hi
??????? 20 ij
??????? 20 mn

6 rows selected

Sql代码??收藏代码
  1. 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代码??收藏代码
  1. 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代码??收藏代码
  1. 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代码??收藏代码
  1. 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代码??收藏代码
  1. 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;

转载注明地址:http://www.chengxuyuans.com/oracle/66219.html