oracle中将不同行内容合并为一行/列转行

ORACLE数据库在处理数据过程中经常会碰到多行合并一行的情况，此情况在处理时曾经利用过下面三种处理方式：

方式一：在数据库中自定义函数，通过循环遍历将取到的值合并后输出。

示例：【取化学元素百分比】

CREATE OR REPLACE FUNCTION F_YRLHT_CFMC_20120824(HTH VARCHAR(20))
 LANGUAGE SQL RETURNS VARCHAR(200)
BEGIN
DECLARE STR_1 VARCHAR(200);
DECLARE STR_COL VARCHAR(200) DEFAULT '';
DECLARE  AT_END  INT  DEFAULT  0;
 DECLARE NOT_FOUND CONDITION FOR SQLSTATE '02000';
DECLARE CONNECT_COL CURSOR WITH RETURN FOR
 SELECT (CASE
         WHEN HXYS IS NULL THEN
          ' '
         ELSE
          HXYS
       END) HXYS
  FROM (SELECT DISTINCT RPAD(TARGETNAME, 10, ' ') HXYS, B.ITEMNO
          FROM TBMPV039 B, TBMPV031 A, TBMPV030 C
          LEFT JOIN TBGP22 F ON C.VENDORNO = F.ID
          LEFT JOIN TBGP10 G ON G.ID = C.VENDORNO,
         (SELECT *
                                   FROM TBMPV002
                                  WHERE TABLEID = 'PRINT_COMMON'
                                    AND TWOCOL = 'COMPNAME'
                                    AND ONECOL = 'BX') D,
         (SELECT *
                                   FROM TBMPV002
                                  WHERE TABLEID = 'ADDRESS') E
         WHERE B.PONO = A.PONO
           AND A.COMPID=B.COMPID
           AND A.PONO = C.PONO
           AND A.COMPID=C.COMPID
           AND A.PURTYPE = '02'
           AND A.PONO = HTH)
 ORDER BY ITEMNO;
 DECLARE  CONTINUE  HANDLER  FOR  NOT_FOUND 
         SET  AT_END  =  1;
 OPEN CONNECT_COL;
     INS_LOOP: 
     LOOP 
 FETCH CONNECT_COL INTO STR_1 ;
 IF AT_END=0 THEN
 SET STR_COL=STR_COL||(CASE WHEN STR_1 IS NULL THEN '            ' ELSE LPAD(STR_1,12,' ') END);
 END IF;
  IF  AT_END  <>0    THEN   
             LEAVE  INS_LOOP; 
         END  IF;
END LOOP;
 RETURN STR_COL;
END

方式二：利用SYS_CONNECT_BY_PATH函数。

首先构建树：
-----------------------------------------------
SELECT ZDRDM, DJH, RN, LEAD(RN) OVER(PARTITION BY ZDRDM ORDER BY RN) RN1
  FROM (SELECT ZDRDM, DJH, ROW_NUMBER() OVER(ORDER BY ZDRDM, DJH DESC) RN
          FROM SG_CG_RKD)
--------------------------------------------------
下面以SG采购入库单表为例：
SELECT ZDRDM, MAX(SYS_CONNECT_BY_PATH(DJH, '--')) RESULT
  FROM (SELECT ZDRDM,
               DJH,
               RN,
               LEAD(RN) OVER(PARTITION BY ZDRDM ORDER BY RN) RN1
          FROM (SELECT ZDRDM,
                       DJH,
                       ROW_NUMBER() OVER(ORDER BY ZDRDM, DJH DESC) RN
                  FROM SG_CG_RKD
                 WHERE ZDRDM = '18972'
                   AND GYSID = '902'
                   AND ROWNUM < 6))
 START WITH RN1 IS NULL
CONNECT BY RN1 = PRIOR RN
 GROUP BY ZDRDM;

 

方式三：利用WM_CONCAT函数。示例如下：

SELECT A.CODE, /*标准编码*/
       B.NAME, /*适应体系*/
       A.NAME, /*标准名称*/
       A.DESCRIPTION, /*管理过程*/
       (CASE
         WHEN (B.ID < 8 OR B.ID > 99 ) AND B.RIGHT = '1' THEN
          ' 管理标准'
         WHEN (B.ID > 8 OR B.ID < 100 ) AND B.RIGHT = '1' THEN
          ' 技术标准'
       END) BZTX, /*标准体系*/
       '' , /*文件层次*/
       D.NAME, /*编制部门*/ /*需要汉化表*/
       E.REALNAME, /*上传人*/
       A.PERMITUSER, /*审核人*/
       A.EXAMINER, /*批准人*/
       WM_CONCAT(C.VERNAME) OVER(PARTITION BY A.ID) VERNAME, /*版本*/
       A.UPDATETIME /*实施日期*/
  FROM FILESINFO           A,
       FCODETYPEINFO       B,
       VW_FILESVERSIONINFO C,
       DEPTINFO            D,
       USERINFO            E
 WHERE A.ID = C.FILESID
   AND A.DEPTID = D.ID
   AND A.UPLOADUSERID = E.ID
   AND A.CODE = 'GF/M 125 003'
   AND A.UPDATETIME = '2016-04-22'
   AND B.NAME = ' 综合管理体系'

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9869601/viewspace-2125191/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9869601/viewspace-2125191/