【轉】Oracle: wmsys.wm_concat、sys_connect_by_path、自定義函式實現行列轉換

構建測試表：
Sql程式碼
create table TABLE1  
(  
  ID   INTEGER,  
  NAME VARCHAR2(10)  
)  
 
create table TABLE2  
(  
  ID   INTEGER,  
  ROLE VARCHAR2(10)  
)  
 
insert into TABLE1 (ID, NAME) values (1, '張三');  
insert into TABLE1 (ID, NAME) values (2, '李四');  
commit;  
 
insert into TABLE2 (ID, ROLE) values (1, '查詢');  
insert into TABLE2 (ID, ROLE) values (1, '分析');  
insert into TABLE2 (ID, ROLE) values (1, '決策');  
insert into TABLE2 (ID, ROLE) values (2, '查詢');  
commit; 
 
要求輸出結果：
Sql程式碼
ID  NAME    ROLE  
    張三 查詢,分析,決策  
    李四 查詢 
 
 
方法一、使用wmsys.wm_concat
Sql程式碼
select table1.*,wmsys.wm_concat(role) from table1,table2 where table1.id=table2.id  
group by table1.id,table1.name 
 
方法二、使用sys_connect_by_path
Sql程式碼
select id, name, ltrim(max(sys_connect_by_path(role, ',')), ',') from   
(select row_number() over(partition by table1.id order by name) rn,table1.*, role from table1, table2  where table1.id =   
 
table2.id)  
start with rn = 1  
connect by prior rn = rn - 1 and prior id = id  
group by id, name 
order by id  
        
 
方法三、使用自定義函式
Sql程式碼
create or replace function my_concat(mid in integer) return varchar2       --記住：引數和返回值裡的資料型別都不用定義長度  
is 
result varchar2(4000);    --定義變數，記住Oracle中定義變數不需要  
begin 
       for temp_cursor in (select role from table2 where id=mid) loop     --此處在遊標FOR迴圈中使用查詢  
           result :=result || temp_cursor.role || ',';    --Oracle中字元連線使用||，而sql server中用+         
       end loop;  
       result := rtrim(result,',');  --去掉最後一個空格,還有Oracle中的賦值前面沒有set  
       return result;  
end;  
 
select table1.*,my_concat(table1.id) from table1,table2 where table1.id=table2.id  
group by table1.id,table1.name 
order by table1.id