将逗号分隔的值转换为多行 [英] converting comma separated value to multiple rows

问题描述

我有一个这样的表:

ID NAME Dept_ID
1  a     2,3
2  b
3  c     1,2

部门是另一个具有dept_id和dept_name作为列的表.我想要类似的结果，

Department is another table having dept_id and dept_name as columns. i want result like,

ID Name Dept_ID
 1   a    2
 1   a    3
 2   b
 3   c    1
 3   c    2

有什么帮助吗?

推荐答案

您可以按照以下步骤操作:

You can do it as:

--Dataset Preparation 
with tab(ID, NAME,Dept_ID) as (Select 1, 'a', '2,3' from dual
                               UNION ALL
                               Select  2,  'b','' from dual
                               UNION ALL
                               Select 3,  'c' ,  '1,2' from dual)      
--Actual Query                      
select distinct ID, NAME, regexp_substr(DEPT_ID,'[^,]+', 1, level) 
from tab    
connect by  regexp_substr(DEPT_ID,'[^,]+', 1, level) is not null
order by 1;

根据我需要加入哪一列?在一张桌子上我有逗号 分隔的ID，在其他表格中，我只有ID

based on which column i need to join? in one table i have comma separated ids and in other table i have just ids

with tab(ID, NAME,Dept_ID) as (Select 1, 'a', '2,3' from dual
                               UNION ALL
                               Select  2,  'b','' from dual
                               UNION ALL
                               Select 3,  'c' ,  '1,2' from dual) ,
      --Table Dept
      tbl_dept (dep_id,depname) as ( Select 1,'depa' from dual
                                       UNION ALL
                                      Select 2,'depb' from dual 
                                      UNION ALL
                                      Select 3,'depc' from dual      
                                    ) ,      
       --Seperating col values for join. Start your query from here using with clause since you already have the two tables.                            
       tab_1 as (select distinct ID, NAME, regexp_substr(DEPT_ID,'[^,]+', 1, level) col3 
                from tab  
                connect by  regexp_substr(DEPT_ID,'[^,]+', 1, level) is not null
                order by 1)
--Joining table.                
Select t.id,t.name,t.col3,dt.depname
from tab_1 t
left outer join tbl_dept dt
on t.col3 = dt.dep_id
order by 1

这篇关于将逗号分隔的值转换为多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！