Oracle 11g Release 2

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

create table table1(id, col, val, rec_pos) as (
    select 1, 'Test 1',  '10',    1 from dual union all
    select 1, 'Test 2',  '20',    2 from dual union all
    select 1, 'Test 3',  '30',    3 from dual union all
    select 1, 'Test 2',  '20',    4 from dual union all
    select 1, 'Empty',   '0101',  5 from dual union all
    select 1, 'Invalid', '1011',  6 from dual union all
    select 2, 'Test 4',  '30',    2 from dual union all
    select 2, 'Test 5',  '30',    3 from dual union all
    select 2, 'Test 6',  '30',    4 from dual union all
    select 2, 'Test 5',  '30',    5 from dual union all
    select 2, 'Empty',   '11101', 6 from dual union all
    select 2, 'Invalid', '10101', 7 from dual union all
    select 3, 'Test 7',  '30',    5 from dual union all
    select 3, 'Test 8',  '30',    6 from dual union all
    select 3, 'Test 8',  '30',    7 from dual union all
    select 3, 'Test 8',  '30',    8 from dual union all
    select 3, 'Empty',   '11110101',  9 from dual union all
    select 3, 'Invalid', '10010101', 10 from dual union all
    select 4, 'Test 9',  '30',    3 from dual union all
    select 4, 'Empty',   '1101',  5 from dual union all
    select 4, 'Invalid', '1011',  6 from dual );
21 rows affected

    select t.*, 
           nvl(substr(max(case col when 'Empty' then val end) 
                         over (partition by id), rec_pos, 1), 0) empty,
           nvl(substr(max(case col when 'Invalid' then val end) 
                         over (partition by id), rec_pos, 1), 0) invalid
      from  table1 t 
      order by id, rec_pos;


ID
COL
VAL
REC_POS
EMPTY
INVALID


1
Test 1
10
1
0
1

1
Test 2
20
2
1
0

1
Test 3
30
3
0
1

1
Test 2
20
4
1
1

1
Empty
0101
5
0
0

1
Invalid
1011
6
0
0

2
Test 4
30
2
1
0

2
Test 5
30
3
1
1

2
Test 6
30
4
0
0

2
Test 5
30
5
1
1

2
Empty
11101
6
0
0

2
Invalid
10101
7
0
0

3
Test 7
30
5
0
0

3
Test 8
30
6
1
1

3
Test 8
30
7
0
0

3
Test 8
30
8
1
1

3
Empty
11110101
9
0
0

3
Invalid
10010101
10
0
0

4
Test 9
30
3
0
1

4
Empty
1101
5
0
0

4
Invalid
1011
6
0
0


    select t.*, 
           nvl(substr((select val 
                        from table1 e 
                        where e.id = t.id and col= 'Empty'), rec_pos, 1), 0) empty,
           nvl(substr((select val 
                        from table1 i 
                        where i.id = t.id and col= 'Invalid'), rec_pos, 1), 0) invalid
      from  table1 t
      order by id, rec_pos;


ID
COL
VAL
REC_POS
EMPTY
INVALID


1
Test 1
10
1
0
1

1
Test 2
20
2
1
0

1
Test 3
30
3
0
1

1
Test 2
20
4
1
1

1
Empty
0101
5
0
0

1
Invalid
1011
6
0
0

2
Test 4
30
2
1
0

2
Test 5
30
3
1
1

2
Test 6
30
4
0
0

2
Test 5
30
5
1
1

2
Empty
11101
6
0
0

2
Invalid
10101
7
0
0

3
Test 7
30
5
0
0

3
Test 8
30
6
1
1

3
Test 8
30
7
0
0

3
Test 8
30
8
1
1

3
Empty
11110101
9
0
0

3
Invalid
10010101
10
0
0

4
Test 9
30
3
0
1

4
Empty
1101
5
0
0

4
Invalid
1011
6
0
0

ID	COL	VAL	REC_POS	EMPTY	INVALID
1	Test 1	10	1	0	1
1	Test 2	20	2	1	0
1	Test 3	30	3	0	1
1	Test 2	20	4	1	1
1	Empty	0101	5	0	0
1	Invalid	1011	6	0	0
2	Test 4	30	2	1	0
2	Test 5	30	3	1	1
2	Test 6	30	4	0	0
2	Test 5	30	5	1	1
2	Empty	11101	6	0	0
2	Invalid	10101	7	0	0
3	Test 7	30	5	0	0
3	Test 8	30	6	1	1
3	Test 8	30	7	0	0
3	Test 8	30	8	1	1
3	Empty	11110101	9	0	0
3	Invalid	10010101	10	0	0
4	Test 9	30	3	0	1
4	Empty	1101	5	0	0
4	Invalid	1011	6	0	0