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/

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值