文档章节

Oracle行转列

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

精选30+云产品,助力企业轻松上云!>>>

如果是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

竹逸风
粉丝 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 分析函数

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
888
0
使用Oracle 11g函数Pivot实现数据聚合行转列

今天在浏览oracle优化、调优相关的博客,无意中看到了oracle的pivot函数,学习了,为了以后复习,先收藏了,下面是转载的博客和相关博客。 转载博客:http://blog.itpub.net/17203031/viewsp...

王景帅
2014/09/22
0
0
Oracle查询如何才能行转列?-sunziren

  原创文章,转载务必注明出处。   今天工作的时候,碰到一个问题,涉及oracle行转列,用了半小时解决,因此在这里写个博客记录一下解决办法。   原数据库表的数据是:      想要达...

osc_6ruay563
01/14
2
0

没有更多内容

加载失败,请刷新页面

加载更多

Maven中dependencyManagement和依赖关系之间的区别

问题: What is the difference between dependencyManagement and dependencies ? dependencyManagement和dependencies什么区别? I have seen the docs at Apache Maven web site. 我已经在......

富含淀粉
21分钟前
7
0
Confluence 如何查看页面 ID

如果你希望查看页面的 ID 你有 2 个方法。 例如,你希望查看 https://www.cwiki.us/display/CONFLUENCEWIKI/Get+started 页面的 Page ID 的话。 如果你的标题栏没有特殊字符,那么将会使用英...

honeymoose
24分钟前
7
0
Linux系统 Centos7 环境基于Docker部署Rocketmq服务

消息队列 基本概述 MQ,Message Queue,基于TCP协议构建的简单协议,区别于具体的通信协议。 基于通信协议定义和抽象的更高层次的通信模型,一般都是生产者和消费者模型,又或者说服务端和客户端...

mazhilin
38分钟前
12
0
我可以在.gitconfig中为自己指定多个用户吗? - Can I specify multiple users for myself in .gitconfig?

问题: In my ~/.gitconfig , I list my personal email address under [user] , since that's what I want to use for Github repos. 在~/.gitconfig ,我在[user]我的个人电子邮件地址,因......

法国红酒甜
51分钟前
19
0
C++11好用的写法

https://stackoverflow.com/questions/12944002/capture-by-value-class-members

剑是SR剑
55分钟前
14
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部