2011年oracle 多行合并一行方法

【51Test.NET-2011年oracle 多行合并一行方法】：

有不少朋友问到这样的问题，如果是oracle10g以上 一个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一次性搞定，

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

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