oracle不同列字段字符串连接成一个字符串

最新推荐文章于 2023-07-21 11:02:58 发布

iteye_15720

最新推荐文章于 2023-07-21 11:02:58 发布

阅读量332

点赞数

分类专栏： Oracle 文章标签：数据库

本文链接：https://blog.csdn.net/iteye_15720/article/details/82374640

版权

Oracle 专栏收录该内容

14 篇文章 0 订阅

订阅专栏

Oracle不同列字段字符连接成一个字符串简单例子：

Test表结构：

no---number(22) text---varchar2(200)

数据：

1, 'abc'

1, 'de'

2, 'fgh'

2, 'ijk'

要实现的查询效果：

1, 'abc';'de'

2, 'fgh';'ijk'

sql代码:

select no,
       ltrim(max(sys_connect_by_path(text, ';')), ';') as texts
     from (select no,
               text,
               rnFirst,
               lead(rnFirst) over(partition by no order by rnFirst) rnNext
      from (select no,text,
                   row_number() over(order by no, text desc) rnFirst
                  from Test a) tmpTable1) tmpTable2
      start with rnNext is null
      connect by rnNext = prior rnFirst
      group by no;

------------------------------------------------------------------------------------------------------

项目中用到的一个实例，够BT的，代码：

select ID,
       PRO_NAME,
       PFCAT_NAME,
       PROTYPE_NAME,
       ROLE_CODE,
       PRO_CODE,
       ltrim(max(sys_connect_by_path(USER_NAME, ';')), ';') as ADM_USERS
from (select ID,
               PRO_NAME,
               PFCAT_NAME,
               PROTYPE_NAME,
               USER_NAME,
               ROLE_CODE,
               PRO_CODE,
               rnFirst,
               lead(rnFirst) over(partition by id order by rnFirst) rnNext
          from (select a.ID,
                       a.PRO_NAME,
                       a.PFCAT_NAME,
                       a.PROTYPE_NAME,
                       a.USER_NAME,
                       a.ROLE_CODE,
                       a.PRO_CODE,
                       row_number() over(order by id desc) rnFirst
                  from (select pro.ID,
                               pro.PFCAT_CODE,
                               pro.BANK_PRO_CODE PRO_CODE,
                               pro.PRO_NAME,
                               pfcat.PFCAT_NAME,
                               PT.PROTYPE_NAME,
                               pm.ROLE_CODE,
                               temp.USER_NAME,
                               pfcat.PFCAT_CODE
                          from products pro
                          join PRO_ROLE_MAP pm on pro.PFCAT_CODE =
                                                  pm.PFCAT_CODE
                          join ADM_POSITIONS ap on ap.ROLE_CODE = pm.ROLE_CODE
                          join PRO_PFCATS pfcat on pro.PFCAT_CODE =
                                                   pfcat.PFCAT_CODE
                          join PRO_TYPES pt ON pro.PROTYPE_CODE =
                                               pt.PROTYPE_CODE
                          left join (SELECT au.USER_NAME, ap.ROLE_CODE
                                      FROM ADM_USERS au
                                      join ADM_USER_POS_MAP map ON au.USER_CODE =
                                                                   map.USER_CODE
                                      join ADM_POSITIONS ap ON map.POS_CODE =
                                                               ap.POS_CODE) temp on temp.ROLE_CODE =
                                                                                    pm.ROLE_CODE
                         where pro.source_type = 'SYS'
                           AND pfcat.pfcat_code = 'TRUST'
                           AND PRO.PRO_CODE NOT in
                               (select distinct p.pro_code
                                  from products_logs log
                                  join products p on log.pro_code = p.pro_code
                                 inner JOIN WKF_EVENTS WE ON log.WFE_ID = WE.ID
                                 WHERE WE.WFG_CODE in
                                       ('WFG_BOND', 'WFG_BROKE', 'WFG_FOREX',
                                        'WFG_FUND', 'WFG_GOLDFX', 'WFG_INS',
                                        'WFG_SAVING', 'WFG_SHARE', 'WFG_SNOTE',
                                        'WFG_TRUST', 'WFG_PROSYS')
                                   AND WE.STEP_STATUS = 'END'
                                   AND WE.ACTION = 'ACCEPT'
                                   AND p.pfcat_code = 'TRUST')) a) tmpTable1) tmpTable2
start with rnNext is null
connect by rnNext = prior rnFirst
group by ID, PRO_NAME, PFCAT_NAME, PROTYPE_NAME, ROLE_CODE, PRO_CODE;