Oracle List 功能 《=》 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   1    张三 查询,分析,决策   2    李四 查询    方法一、使用wmsys.wm_concatSql代码select table1.*,wmsys.wm_concat(role) from table1,table2 where table1.id=table2.id   group by table1.id,table1.name   方法二、使用sys_connect_by_pathSql代码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