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

(责任编辑：admin)

Oracle分析函数——SUM,AVG,MIN,MAX,COUNT	多表关联的update语句
使用一条INSERT语句完成多表插入	Oracle的redo和undo的区别
Oracle表被锁的解决方案	Oracle分析函数案例
Oracle游标使用大全	Oracle创建、管理撤销表空间
Oracle数据库的归档日志写满磁盘空间解决办法	Oracle批量清空所有表数据
特殊需求下Oracle分页的加速	处理Oracle中TEMP表空间满的问题(含查询表空间SQL)
oracle表加索引反而查询慢的原因	ORACLE字符集问题
Oracle进行数据库查询的小技巧	oracle中的游标使用静态游标
Oracle游标使用总结	wmsys.wm_concat、sys_connect_by_path、自定义函数实

ASP.NET学习网|ASP.NET教程|ASP.NET MVC|ASP.NET 源码|VS2010教程|ASP.NET视频教程

热门标签

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

Tags：行列转换