wmsys.wm_concat、sys_connect_by_path、自定义函数实现行列转换

无论是在做Oracle数据库开发还是在运维的过程中，我们经常都会使用到行列转换操作，Oracle为行列转换提供了一些内置函数，下面我们对比一下使用这些函数如何实现行列转换。
1、创建测试需要使用的表
SQL> CREATE TABLE tangyun(PID NUMBER NOT NULL PRIMARY KEY,cName VARCHAR2(20));

表已创建。

SQL> CREATE TABLE tangyun_sub(SID NUMBER NOT NULL,pName VARCHAR2(20));

表已创建。
2、在测试表中插入一些数据
SQL> INSERT INTO tangyun VALUES (1,'电子政务');
已创建 1 行。
SQL> INSERT INTO tangyun VALUES (2,'IT工程部');
已创建 1 行。
SQL> INSERT INTO tangyun VALUES (3,'市场部');
已创建 1 行。
SQL> INSERT INTO tangyun VALUES (4,'大社保部');
已创建 1 行。
SQL> COMMIT;
提交完成。
SQL> INSERT INTO tangyun_sub VALUES (1,'执法办案组');
已创建 1 行。
SQL> INSERT INTO tangyun_sub VALUES (1,'管理防范组');
已创建 1 行。
SQL> INSERT INTO tangyun_sub VALUES (1,'维护组');
已创建 1 行。
SQL> INSERT INTO tangyun_sub VALUES (1,'应用组');
已创建 1 行。
SQL> INSERT INTO tangyun_sub VALUES (1,'数据组');
已创建 1 行。
SQL> INSERT INTO tangyun_sub VALUES (2,'售后服务');
已创建 1 行。
SQL> INSERT INTO tangyun_sub VALUES (2,'产品服务');
已创建 1 行。
SQL> INSERT INTO tangyun_sub VALUES (2,'技术支持');
已创建 1 行。
SQL> INSERT INTO tangyun_sub VALUES (3,'客户服务部');
已创建 1 行。
SQL> INSERT INTO tangyun_sub VALUES (3,'产品中心');
已创建 1 行。
SQL> INSERT INTO tangyun_sub VALUES (3,'销售部');
已创建 1 行。
SQL> INSERT INTO tangyun_sub VALUES (4,'应用组');
已创建 1 行。
SQL> INSERT INTO tangyun_sub VALUES (4,'维护组');
已创建 1 行。
SQL> INSERT INTO tangyun_sub VALUES (4,'数据组');
已创建 1 行。
SQL> COMMIT;
提交完成。

3、下面对比两个函数实现行列转换

a、使用wmsys.wm_concat()函数实现行列转换
SQL> SELECT t1.PID "pid",t1.cName "cname",wmsys.wm_concat(t2.pName) "sub_name" FROM tangyun t1,tangy
un_sub t2 WHERE t1.PID=t2.SID GROUP BY t1.cName,t1.Pid order by pid;

pid cname sub_name
---------- ---------- --------------------------------------------------
1 电子政务 执法办案组,管理防范组,维护组,数据组,应用组
2 IT工程部 售后服务,产品服务,技术支持
3 市场部 客户服务部,销售部,产品中心
4 大社保部 应用组,维护组,数据组

b、使用sys_connect_by_path函数实现行列转换
SQL> select pid, cName, ltrim(max(sys_connect_by_path(pName, ',')), ',') "sub_name" from (select row
_number() over(PARTITION by t1.pid ORDER by cName) r,t1.*, t2.pName from tangyun t1, tangyun_sub t2
where t1.pid = t2.sid)
2 start with r=1 CONNECT by prior r =r-1 and prior pid = pid group by pid ,cName order by pid;

PID CNAME sub_name
---------- ---------- --------------------------------------------------
1 电子政务 执法办案组,管理防范组,维护组,应用组,数据组
2 IT工程部 售后服务,产品服务,技术支持
3 市场部 客户服务部,产品中心,销售部
4 大社保部 应用组,维护组,数据组
c、使用自定义函数实现行列转换
由于使用自定义函数实现起来比较简单，这里就不举例子了，这里只是为了复习一下wmsys.wm_concat、sys_connect_by_path两个函数的用法。

－－－－－－－－－－－－－－End－－－－－－－－－－－－－－－－－－－－－－－－－－

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24930246/viewspace-1041637/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24930246/viewspace-1041637/