逗号分隔的字段怎么怎么分别取出关联查询？

[wai631]

表A如图，B表如图。现在需要把AB字段按B表的对应转换中文，印象中CONNECT BY 可以，写几次都失败了，请帮忙看看该怎么写SQL？谢谢

[卖水果的net]

-- 这样拆分

with m as (
select LX, DM, regexp_substr(QX, '[^,]+',1,level) new_item
from t1 connect by level <= regexp_count(qx,',') + 1 
and prior rowid = rowid 
and prior dbms_random.value is not null
)
select * from m

[卖水果的net]

两张表的数据，给些示例，要 insert 形式的。

[碧水幽幽泉]

这个问题比较简单，请给出建表语句和测试数据。

[wai631]

感谢两位回复。测试数据如下。

/*
drop table T1;
drop table T2;
*/
CREATE TABLE T1 (
 LX VARCHAR2(10),
 DM VARCHAR2(20),
 QX VARCHAR2(3000)
 );
 insert into T1 values('CK07','c512','about, accept, cancel, controlg, controlp, controlr, exit, help, locale, qbe_save, qbe_select');
 insert into T1 values('CK07','mp002','about, accept, cancel, close, controlg, controlp, controlr, exit, help, locale, qbe_save, qbe_select');
 insert into T1 values('CK08','mp306','about, accept, cancel, controlg, controlp, controlr, controls, execute, exit, give_up, help, locale, qbe_save, qbe_select, select_cancel');
 insert into T1 values('CK08','mp700','about, accept, cancel, controlg, controlp, controlr, exit, help, locale, mntn_doc_pty');
 insert into T1 values('CK09','mp702','about, accept, cancel, controlg, controlp, controlr, exit, help, locale, mntn_reason');
 
CREATE TABLE T2 (
AN VARCHAR2(80),
BZ VARCHAR2(20),
ZW VARCHAR2(80)
);
 insert into T2 values('about','standard','程序资讯');
 insert into T2 values('accept','standard','确定更新');
 insert into T2 values('cancel','standard','取消');
 insert into T2 values('close','mp002','');
 insert into T2 values('close','standard','离开');
 insert into T2 values('exit','standard','结束');
 insert into T2 values('give_up','standard','放弃');
 insert into T2 values('help','standard','帮助');
 insert into T2 values('jump','standard','指定笔');
 insert into T2 values('last','standard','末一笔');
 insert into T2 values('locale','c512','简易输入');
 insert into T2 values('locale','standard','语言');
 insert into T2 values('qbe_save','standard','条件储存');

需求是从T1的QX在T2表关联AN 取zw ,条件是当T1.DM=T2.BZ AND T2.ZW IS NOT NULL ，否则standard 对应即可，T2无数据的还是显示英文。
想要的结果如下：
CK08 mp306 程序资讯,确定更新,取消,开窗查询,开启查询视窗,必要栏位,结束,帮助,简易输入,语言,条件储存,条件查询

[liucaili8527]

太棒了，非常感谢

[yaiger]

结果确认无误的话，请及时结贴