table1
id1 name1
a asdf
b qwertable2
id2 name2 id1
1 ssss a
2 sss a
3 sssss b
4 ss b
怎么得到如下结果,即把table2中所有对应id1的信息连成一个字符串
id1 name1 desc1
a asdf 1|ssss;2|sss
b qwer 3|sssss;4|ss
id1 name1
a asdf
b qwertable2
id2 name2 id1
1 ssss a
2 sss a
3 sssss b
4 ss b
怎么得到如下结果,即把table2中所有对应id1的信息连成一个字符串
id1 name1 desc1
a asdf 1|ssss;2|sss
b qwer 3|sssss;4|ss
(
select 'a' id1, 'asdf' name1 from dual
union all
select 'b' id1, 'qwer' name1 from dual
),
tmp2 as
(
select 1 id2, 'ssss' name2, 'a' id1 from dual
union all
select 2 id2, 'sss' name2, 'a' id1 from dual
union all
select 3 id2, 'sssss' name2, 'b' id1 from dual
union all
select 4 id2, 'ss' name2, 'b' id1 from dual
)
select tmp2.id1,tmp1.name1,wm_concat(tmp2.id2 || '|' || tmp2.name2)
from tmp2, tmp1
where tmp2.id1 = tmp1.id1
group by tmp2.id1,tmp1.name1;
select id1,name1,desc1
from (select id1,
name1,
desc1,
row_number() over(partition by id1, name1 order by id2 desc) rn
from (select id1,
id2,
name1,
substr(sys_connect_by_path(id2 || '|' || name2, ';'),
2) desc1
from (select t.*,
row_number() over(partition by id1 order by id2) rn,
t.id1 || row_number() over(partition by id1 order by id2) rn1,
t.id1 ||
(row_number()
over(partition by id1 order by id2) - 1) rn2
from (select t1.id1, t1.name1, t2.id2, t2.name2
from test1 t1, test2 t2
where t1.id1 = t2.id1) t)
start with rn = 1
connect by rn2 = prior rn1))
where rn = 1id1 name1 desc1
-----------------------
a asdf 1|ssss;2|sss
b qwer 3|sssss;4|ss