Oracle行转列-CSDN博客

2019独角兽企业重金招聘Python工程师标准>>>

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