Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table t1(id, lang, assurance, val) as (
    select 1, 'L1', 'ASSR1', 0 from dual union all
    select 1, 'L2', 'ASSR2', 1 from dual union all
    select 1, 'L3', 'ASSR3', 5 from dual );
3 rows affected

create table t2(id, lang, file_, assurance, val) as (
    select 1, 'L1', 'F1', 'ASSR1', 0 from dual union all
    select 1, 'L1', 'F2', 'ASSR1', 4 from dual union all
    select 1, 'L2', 'F3', 'ASSR2', 8 from dual union all
    select 1, 'L2', 'F4', 'ASSR2', 3 from dual union all
    select 1, 'L3', 'F5', 'ASSR3', 7 from dual union all
    select 1, 'L3', 'F6', 'ASSR3', 0 from dual );
6 rows affected

select id, file_, assurance, val
  from (
    select id, lang, lang file_, assurance, val, 't1' source from t1 where id = 1
    union all
    select id, lang, file_, assurance, val, 't2' source from t2 where id = 1)
  order by lang, source, file_;
  


ID
FILE_
ASSURANCE
VAL


1
L1
ASSR1
0

1
F1
ASSR1
0

1
F2
ASSR1
4

1
L2
ASSR2
1

1
F3
ASSR2
8

1
F4
ASSR2
3

1
L3
ASSR3
5

1
F5
ASSR3
7

1
F6
ASSR3
0

ID	FILE_	ASSURANCE	VAL
1	L1	ASSR1	0
1	F1	ASSR1	0
1	F2	ASSR1	4
1	L2	ASSR2	1
1	F3	ASSR2	8
1	F4	ASSR2	3
1	L3	ASSR3	5
1	F5	ASSR3	7
1	F6	ASSR3	0