主表 T1ID Name
1 AAA
2 BBB
3 CCC子表 T2
ID Value1,Value2
1 123 456
1 333 666
2 345 999
2 008 444
3 567 4454
3 45 56777
3 555 6789想做一个视图联合查询达到以下效果,请教如何做
ID Name Value
1 AAA 123-456,333-666
2 BBB 345-999,008-444
3 CCC 567-4454,45-56777,555-6789
1 AAA
2 BBB
3 CCC子表 T2
ID Value1,Value2
1 123 456
1 333 666
2 345 999
2 008 444
3 567 4454
3 45 56777
3 555 6789想做一个视图联合查询达到以下效果,请教如何做
ID Name Value
1 AAA 123-456,333-666
2 BBB 345-999,008-444
3 CCC 567-4454,45-56777,555-6789
select distinct
yy.id,
T1.name,
ltrim(first_value(path) over(partition by yy.id order by lev desc)) as values_nums
from (
select zz.*,
sys_connect_by_path(zz.values_num,',') as path,
level lev
from (
select tt.id,
tt.Value1||'-'||tt.Value2 as values_num,
tt.id||(row_number() over(partition by tt.id)-1) front_data,
tt.id||row_number() over(partition by tt.id) behind_data
from T2 tt
)zz
connect by prior zz.front_data = zz.behind_data
)yy,
T1
where yy.id = T1.id;