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

构建测试表：
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;  
要求输出结果：
ID  NAME    ROLE   
1    张三查询,分析,决策   
2    李四查询 
     
 

方法一、使用wmsys.wm_concat

select table1.*,wmsys.wm_concat(role) from table1,table2 where table1.id=table2.id  

group by table1.id,table1.name 

方法二、使用sys_connect_by_path

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

方法三、使用自定义函数

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 

文章出自：http://blog.sina.com.cn/s/blog_6853c03b0100sedn.html