文档章节

Oracle行转列

竹逸风
 竹逸风
发布于 2016/06/22 15:04
字数 1047
阅读 15
收藏 1

如果是oracle 10g以上 一个wm_concat函数就可以解决。

如果是oracle 9i以下就有点麻烦了。

表结构如下:

NAME Null Type


N_SEC_CODE NOT NULL CHAR(6)

C_RESEARCHER_CODE NOT NULL VARCHAR2(20)

此表保存了“股票”与“研究员”的对应关系数据,一般而言,对于同一只股票而言,可能有多个研究员

对其进行跟踪研究。所以目前遇到一个要求,需查询出每只股票和对应的研究员(研究员代码间,使用逗号分隔)。

例如有如下数据:

000297 chenpeng

000297 liusu

合并处理后需显示为:

000297 chenpeng,liusu

网上查了很多方法,但通常而言都是编写自定义多行文本合并函数,或者对支持的列数具有局限性。

最后在英文google中搜到如下比较巧的方法。不用在数据库中增加function,纯SQL一次性搞定,

而且扩充性很好,没有列的限制。

ORACLE纯SQL实现多行合并一行 - 南乡子 - 旅夜书怀ORACLE纯SQL实现多行合并一行 - 南乡子 - 旅夜书怀Code

SELECT n_sec_code, TRANSLATE (LTRIM (text, '/'), '/', ',') researcherList

 FROM (SELECT ROW_NUMBER () OVER (PARTITION BY n_sec_code ORDER BY n_sec_code,

               lvl DESC) rn,

              n_sec_code, text

         FROM (SELECT      n_sec_code, LEVEL lvl,

                          SYS_CONNECT_BY_PATH (c_researcher_code,'/') text

                     FROM (SELECT    n_sec_code, c_researcher_code as c_researcher_code,

                                    ROW_NUMBER () OVER (PARTITION BY n_sec_code ORDER BY n_sec_code,c_researcher_code) x

                               FROM m_researcher_stock_rel

                           ORDER BY n_sec_code, c_researcher_code) a

               CONNECT BY n_sec_code = PRIOR n_sec_code AND x - 1 = PRIOR x))

WHERE rn = 1

ORDER BY n_sec_code;

预想的结果成功出现,多行数据成功汇总到一行,特此分享与大家。对于你自己的应用中,只需要把SQL中“n_sec_code”

换为你的用来汇总的列,“c_researcher_code”替换为需合并文本的列,“m_researcher_stock_rel”替换为你的表名,就是这么简单。

SQL分析:

1、利用 “ROW_NUMBER () OVER (PARTITION BY……” 为按“股票代码”汇总后的数据行添加组内序号

2、“SYS_CONNECT_BY_PATH” 按组内序号相邻关系,为每一层进行不同行的“研究员代码”叠加

3、再次利用“股票代码”进行组内分组,但按第二部中的层次排倒序,增加调整后等级

4、取所有调整后等级为1的结果,即为所要求的数据行

方法很巧妙,值得学习。:-)

感谢网友@OctoberOne、@ericqliu的指点,在ORACLE10中可使用以下方法:

SELECT n_sec_code, wmsys.wm_concat (c_researcher_code) as result

FROM m_researcher_stock_rel

GROUP BY n_sec_code

又一个示例: WITH tab AS( select '01' no,'ss' name, 10 jiner,'麦子' you from dual union all select '02' no,'dd' name, 11 jiner,'萝卜' you from dual union all select '03' no,'cc' name, 9 jiner,'大豆' you from dual union all select '01' no,'ss' name, 10 jiner,'白菜' you from dual) SELECT a.no, a.name, a.jiner, ltrim(MAX(sys_connect_by_path(you, ',')), ',') you FROM (SELECT row_number() over(PARTITION BY a.no, a.name, a.jiner ORDER BY a.no, a.name, a.jiner) rn,a.* FROM tab a) a START WITH rn = 1 CONNECT BY PRIOR rn = rn - 1 AND a.no = PRIOR a.no AND a.name = PRIOR a.name AND a.jiner = PRIOR a.jiner GROUP BY a.no, a.name, a.jiner

oracle wm_concat函数,用于列转行,逗号分隔

首先让我们来看看这个神奇的函数wm_concat(列名),该函数可以把列值以","号分隔起来,并显示成一行,接下来上例子,看看这个神奇的函数如何应用 准备测试数据 SQL> create table test(id number,name varchar2(20));

SQL> insert into test values(1,'a');

SQL> insert into test values(1,'b');

SQL> insert into test values(1,'c');

SQL> insert into test values(2,'d');

SQL> insert into test values(2,'e');

SQL> commit;

效果1 : 行转列 SQL> select wm_concat(name) from test;

WM_CONCAT(NAME)


a,b,c,d,e

效果2: 把结果里的逗号替换成"|" SQL> select replace(wm_concat(name),',','|') from test;

REPLACE(WM_CONCAT(NAME),',','|')


a|b|c|d|e

效果3:按ID分组合并name

SQL> select id,wm_concat(name) name from test group by id;

ID NAME


1 a,b,c

2 d,e

懒人扩展用法:

案例:我要写一个视图,类似"create or replace view as select 字段1,...字段50 from tablename" ,基表有50多个字段,要是靠手工写太麻烦了,有没有什么简便的方法? 当然有了,看我如果应用wm_concat来让这个需求变简单

SQL> select 'create or replace view as select '|| wm_concat(column_name) || ' from dept'from user_tab_columns where table_name='DEPT';

'CREATEORREPLACEVIEWASSELECT'||WM_CONCAT(COLUMN_NAME)||'FROMDEPT'


create or replace view as select DEPTNO,DNAME,LOC from dept

本文转载自:http://www.cnblogs.com/xieon1986/archive/2013/01/16/2863077.html

共有 人打赏支持
竹逸风
粉丝 2
博文 12
码字总数 4075
作品 0
北京
后端工程师
oracle 分析函数

Oracle 行列转换函数 create table exam_record(id integer primary key,username varchar2(40),classtype varchar2(40),classname varchar2(200),score number(20,2));insert into exam_re......

guibawudi1985
2017/06/07
0
0
Oracle 行转列pivot 、列转行unpivot 的Sql语句总结

转自:http://blog.csdn.net/xiaokui_wingfly/article/details/42419207 多行转字符串 这个比较简单,用||或concat函数可以实现 实际上就是拆分字符串的问题,可以使用 substr、instr、regex...

Amui
2016/06/29
373
0
ORACLE 列转行和行转列的SQL和函数

本文介绍两种行、列转换方法,一种是常规的SQL,一种是ORACLE 11G提供的函数。下面使用一个小例子对这两种方法进行说明。 ---------------------------------原始表-----------------------...

moonstarseu
2012/03/19
0
0
oracle wmsys.wm_concat

oracle,行转列函数 select s.newspartbigtype_id, wmsys.wmconcat(s.equiptype) as equip_type from automation.AUTO_NEWSPARTSMALLTYPE1 s group by s.newspartbigtype_id......

zhengqingping
2014/03/20
0
0
【书评:Oracle查询优化改写】第14章 结尾章

【书评:Oracle查询优化改写】第14章 结尾章 一.1 相关参考文章链接 前13章的链接参考相关连接: 【书评:Oracle查询优化改写】第一章 http://blog.itpub.net/26736162/viewspace-1652985/ 【书...

技术小胖子
2017/11/08
0
0

没有更多内容

加载失败,请刷新页面

加载更多

如何通过 J2Cache 实现分布式 session 存储

做 Java Web 开发的人多数都会需要使用到 session (会话),我们使用 session 来保存一些需要在两个不同的请求之间共享数据。一般 Java 的 Web 容器像 Tomcat、Resin、Jetty 等等,它们会在...

红薯
今天
3
0
C++ std::thread

C++11提供了std::thread类来表示一个多线程对象。 1,首先介绍一下std::this_thread命名空间: (1)std::this_thread::get_id():返回当前线程id (2)std::this_thread::yield():用户接口...

yepanl
今天
3
0
Nignx缓存文件与动态文件自动均衡的配置

下面这段nginx的配置脚本的作用是,自动判断是否存在缓存文件,如果有优先输出缓存文件,不经过php,如果没有,则回到php去处理,同时生成缓存文件。 PHP框架是ThinkPHP,最后一个rewrite有关...

swingcoder
今天
1
0
20180920 usermod命令与用户密码管理

命令 usermod usermod 命令的选项和 useradd 差不多。 一个用户可以属于多个组,但是gid只有一个;除了gid,其他的组(groups)叫做扩展组。 usermod -u 1010 username # 更改用户idusermod ...

野雪球
今天
3
0
Java网络编程基础

1. 简单了解网络通信协议TCP/IP网络模型相关名词 应用层(HTTP,FTP,DNS等) 传输层(TCP,UDP) 网络层(IP,ICMP等) 链路层(驱动程序,接口等) 链路层:用于定义物理传输通道,通常是对...

江左煤郎
今天
3
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部