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－－－－－－－－－－－－－－－－－－－－－－－－－－