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

 

构建测试表：

Sql代码

1. create table TABLE1   
2. (   
3.   ID   INTEGER,   
4.   NAME VARCHAR2(10)   
5. )   
6.   
7. create table TABLE2   
8. (   
9.   ID   INTEGER,   
10.   ROLE VARCHAR2(10)   
11. )   
12.   
13. insert into TABLE1 (ID, NAME) values (1, '张三');   
14. insert into TABLE1 (ID, NAME) values (2, '李四');   
15. commit;   
16.   
17. insert into TABLE2 (ID, ROLE) values (1, '查询');   
18. insert into TABLE2 (ID, ROLE) values (1, '分析');   
19. insert into TABLE2 (ID, ROLE) values (1, '决策');   
20. insert into TABLE2 (ID, ROLE) values (2, '查询');   
21. commit;  

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代码

1. ID  NAME    ROLE   
2. 1    张三 查询,分析,决策   
3. 2    李四 查询  

IDNAMEROLE1 张三查询,分析,决策2 李四查询

 

 

方法一、使用wmsys.wm_concat

Sql代码

1. select table1.*,wmsys.wm_concat(role) from table1,table2 where table1.id=table2.id   
2. group by table1.id,table1.name  

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

 

方法二、使用sys_connect_by_path

Sql代码

1. select id, name, ltrim(max(sys_connect_by_path(role, ',')), ',') from    
2. (select row_number() over(partition by table1.id order by name) rn,table1.*, role from table1, table2  where table1.id =    
3.   
4. table2.id)   
5. start with rn = 1   
6. connect by prior rn = rn - 1 and prior id = id   
7. group by id, name  
8. order by id   
9.          

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 = 1connect by prior rn = rn - 1 and prior id = idgroup by id, nameorder by id       

 

方法三、使用自定义函数 

Sql代码

1. create or replace function my_concat(mid in integer) return varchar2       --记住：参数和返回值里的数据类型都不用定义长度  
2. is  
3. result varchar2(4000);    --定义变量，记住Oracle中定义变量不需要  
4. begin  
5.        for temp_cursor in (select role from table2 where id=mid) loop     --此处在游标FOR循环中使用查询  
6.            result :=result || temp_cursor.role || ',';    --Oracle中字符连接使用||，而sql server中用+         
7.        end loop;   
8.        result := rtrim(result,',');  --去掉最后一个空格,还有Oracle中的赋值前面没有set  
9.        return result;   
10. end;   
11.   
12. select table1.*,my_concat(table1.id) from table1,table2 where table1.id=table2.id   
13. group by table1.id,table1.name  
14. order by table1.id