Oracle行转列函数
来源:互联网 发布:c语言putchar 32 编辑:程序博客网 时间:2024/06/06 16:52
create table TABLE1
(
)
create table TABLE2
(
)
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
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
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);
begin
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
以上是转载网上网友的,只有发现还有一个方式:
先看示例代码:
- with temp as(
- select 'China' nation ,'Guangzhou' city from dual union all
- select 'China' nation ,'Shanghai' city from dual union all
- select 'China' nation ,'Beijing' city from dual union all
- select 'USA' nation ,'New York' city from dual union all
- select 'USA' nation ,'Bostom' city from dual union all
- select 'Japan' nation ,'Tokyo' city from dual
- )
- select nation,listagg(city,',') within GROUP (order by city)
- from temp
- group by nation
- with temp as(
- select 'China' nation ,'Guangzhou' city from dual union all
- select 'China' nation ,'Shanghai' city from dual union all
- select 'China' nation ,'Beijing' city from dual union all
- select 'USA' nation ,'New York' city from dual union all
- select 'USA' nation ,'Bostom' city from dual union all
- select 'Japan' nation ,'Tokyo' city from dual
- )
- select nation,listagg(city,',') within GROUP (order by city)
- from temp
- group by nation
这是最基础的用法:
LISTAGG(XXX,XXX) WITHIN GROUP( ORDER BY XXX)
用法就像聚合函数一样,通过Group by语句,把每个Group的一个字段,拼接起来。
非常方便。
同样是聚合函数,还有一个高级用法:
就是over(partition by XXX)
也就是说,在你不实用Group by语句时候,也可以使用LISTAGG函数:
- with temp as(
- select 500 population, 'China' nation ,'Guangzhou' city from dual union all
- select 1500 population, 'China' nation ,'Shanghai' city from dual union all
- select 500 population, 'China' nation ,'Beijing' city from dual union all
- select 1000 population, 'USA' nation ,'New York' city from dual union all
- select 500 population, 'USA' nation ,'Bostom' city from dual union all
- select 500 population, 'Japan' nation ,'Tokyo' city from dual
- )
- select population,
- nation,
- city,
- listagg(city,',') within GROUP (order by city) over (partition by nation) rank
- from temp
- with temp as(
- select 500 population, 'China' nation ,'Guangzhou' city from dual union all
- select 1500 population, 'China' nation ,'Shanghai' city from dual union all
- select 500 population, 'China' nation ,'Beijing' city from dual union all
- select 1000 population, 'USA' nation ,'New York' city from dual union all
- select 500 population, 'USA' nation ,'Bostom' city from dual union all
- select 500 population, 'Japan' nation ,'Tokyo' city from dual
- )
- select population,
- nation,
- city,
- listagg(city,',') within GROUP (order by city) over (partition by nation) rank
- from temp
总结:LISTAGG()把它当作SUM()函数来使用就可以了。
- ORACLE行转列函数
- oracle 函数中的行转列
- oracle行转列函数
- Oracle行转列函数
- oracle函数wm_concat行转列
- Oracle 行转列 wm_concat函数
- oracle 函数 实现行转列
- oracle行转列函数
- oracle 之 行转列函数
- Oracle行转列 decode函数
- Oracle行转列函数wm_concat
- oracle函数pivot】行转列
- oracle pivot函数行转列
- Oracle—decode函数行转列
- Oracle函数
- oracle 函数
- Oracle函数
- Oracle函数
- 自己写的列出盘符下的所有文件树
- Linux -- 内存控制之oom killer机制及代码分析
- VC6.0附加到进程空列表
- windows 中 Eclipse 打开当前文件所在文件夹
- The Unique MST - POJ 1679 次小生成树
- Oracle行转列函数
- 指针内存分配
- LeetCode-Remove Duplicates from Sorted List
- yum
- Nginx 源码下载以及比较好的教程链接
- inline函数的作用
- 有趣的数
- baidu手机浏览器安卓4.5版发布:因为快,所以爱
- robot framework 使用三:浏览器兼容性自动化