WMSYS.WM_CONCAT 函數的用法
来源:互联网 发布:浪漫生日祝福 源码 编辑:程序博客网 时间:2024/06/07 01:06
select t.rank, t.Name from t_menu_item t;
--------------------------------
我们通过 10g 所提供的 WMSYS.WM_CONCAT 函数即可以完成 行转列的效果
DEPTNO ENAME
------ ----------
例子如下:
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;
---------- ------------------------------
6 rows selected
SQL> select id,wmsys.wm_concat(name) name from idtable
2 group by id;
---------- --------------------------------------------------------------------------------
SQL> select id,wmsys.wm_concat(name) over (order by id) name from idtable;
---------- --------------------------------------------------------------------------------
6 rows selected
SQL> select id,wmsys.wm_concat(name) over (order by id,name) name from idtable;
---------- --------------------------------------------------------------------------------
6 rows selected
个人觉得这个用法比较有趣.
SQL> select id,wmsys.wm_concat(name) over (partition by id) name from idtable;
---------- --------------------------------------------------------------------------------
6 rows selected
SQL> select id,wmsys.wm_concat(name) over (partition by id,name) name from idtable;
---------- --------------------------------------------------------------------------------
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
1
2
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;
- WMSYS.WM_CONCAT 函數的用法
- WMSYS.WM_CONCAT 函數的用法
- WMSYS.WM_CONCAT 函數的用法
- WMSYS.WM_CONCAT 函數的用法
- WMSYS.WM_CONCAT 函數的用法
- [转]WMSYS.WM_CONCAT 函數的用法
- WMSYS.WM_CONCAT 函數的用法
- WMSYS.WM_CONCAT 函數的用法
- WMSYS.WM_CONCAT 函數的用法
- WMSYS.WM_CONCAT 函數的用法
- wmsys.wm_concat的用法
- wmsys.wm_concat的几个用法
- wmsys.wm_concat的几个用法
- wmsys.wm_concat的几个用法
- WMSYS.WM_CONCAT 函数的用法
- wmsys.wm_concat的几个用法
- wmsys.wm_concat的几个用法
- wmsys.wm_concat的几个用法 .
- php字符串处理函数大全--有时候我们只需要知道名字。
- Android传感器编程入门
- Android开发环境搭建完全图解
- 通用编号生成器
- 如何简便的测量电池电量
- WMSYS.WM_CONCAT 函數的用法
- SELECT INTO 和 INSERT INTO SELECT 两种表复制语句
- [Happy Coding] 一段调用STL算法的程序代码的效率分析
- 判断点是否在多边形内(2D空间)
- .HibernateDaoSupport详解
- WiEngine入门学习笔记(1)
- ORACLE 11G RAC 配置小结
- 黑马程序员之C#学习笔记:字符串函数
- SQL语句