Oracle 18c

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

create table logs (id number, log_tags varchar2(4000));
begin
    insert into logs (id, log_tags) values ( 1,'<Event time="Sat Apr 15 1:13:17.750" type="Debug" thread="2fec: Main CIM worker thread" elapsed="0" ');
    insert into logs (id, log_tags) values ( 2,'Database: C:\2023 Files\GDBs_4\New Mobile Geodatabase.geodatabase');
    insert into logs (id, log_tags) values ( 3,'      SQL: SELECT main.ACTIVE_TRANSPORTATION.OBJECTID,main.active_transportation_nt_flag_vw.flag FRO');
    insert into logs (id, log_tags) values ( 4,'</Event>');
    
    insert into logs (id, log_tags) values ( 5,'<Event time="Sat Apr 15 1:13:17.749" type="Debug" thread="2fec: Main CIM worker thread" elapsed="1" ');
    insert into logs (id, log_tags) values ( 6,null);
    insert into logs (id, log_tags) values ( 7,'</Event>');
    
    insert into logs (id, log_tags) values ( 8,'<Event time="Sat Apr 15 1:13:17.749" type="Debug" thread="2fec: Main CIM worker thread" elapsed="2" ');
    insert into logs (id, log_tags) values ( 9,'Database: C:\2023 Files\GDBs_4\New Mobile Geodatabase.geodatabase');
    insert into logs (id, log_tags) values (10,'      SQL: SELECT main.ACTIVE_TRANSPORTATION.OBJECTID,main.active_transportation_nt_flag_vw.flag FRO');
    insert into logs (id, log_tags) values (11,'      Number of features returned: 100');
    insert into logs (id, log_tags) values (12,'</Event>');
    
    insert into logs (id, log_tags) values (13,'<Event time="Sat Apr 15 1:13:17.749" type="Debug" thread="2fec: Main CIM worker thread" elapsed="0" ');
    insert into logs (id, log_tags) values (14,null);
    insert into logs (id, log_tags) values (15,'</Event>');
    
    insert into logs (id, log_tags) values (16,'<Event time="Sat Apr 15 1:13:17.747" type="Debug" thread="2fec: Main CIM worker thread" elapsed="0" ');
    insert into logs (id, log_tags) values (17,'Database: C:\2023 Files\GDBs_4\New Mobile Geodatabase.geodatabase');
    insert into logs (id, log_tags) values (18,'      SQL: SELECT main.ACTIVE_TRANSPORTATION.OBJECTID,main.active_transportation_nt_flag_vw.flag FRO');
    insert into logs (id, log_tags) values (19,'</Event>');
    
    insert into logs (id, log_tags) values (20,'<Event time="Sat Apr 15 1:13:17.747" type="Debug" thread="2fec: Main CIM worker thread" elapsed="2" ');
    insert into logs (id, log_tags) values (21,'Database: C:\2023 Files\GDBs_4\New Mobile Geodatabase.geodatabase');
    insert into logs (id, log_tags) values (22,'      SQL: SELECT main.ACTIVE_TRANSPORTATION.OBJECTID,main.active_transportation_nt_flag_vw.flag FRO');
    insert into logs (id, log_tags) values (23,'      Number of features returned: 100');
    insert into logs (id, log_tags) values (24,'</Event>');
    
    insert into logs (id, log_tags) values (25,'<Event time="Sat Apr 15 1:13:17.746" type="Debug" thread="2fec: Main CIM worker thread" elapsed="1" ');
    insert into logs (id, log_tags) values (26,null);
    insert into logs (id, log_tags) values (27,'</Event>');
    
    insert into logs (id, log_tags) values (28,'<Event time="Sat Apr 15 1:13:17.746" type="Debug" thread="2fec: Main CIM worker thread" elapsed="0" ');
    insert into logs (id, log_tags) values (29,null);
    insert into logs (id, log_tags) values (30,'</Event>');
    
    insert into logs (id, log_tags) values (31,'<Event time="Sat Apr 15 1:13:17.744" type="Debug" thread="2fec: Main CIM worker thread" elapsed="0" ');
    insert into logs (id, log_tags) values (32,'Database: C:\2023 Files\GDBs_4\New Mobile Geodatabase.geodatabase');
    insert into logs (id, log_tags) values (33,'      SQL: SELECT main.ACTIVE_TRANSPORTATION.OBJECTID,main.active_transportation_nt_flag_vw.flag FRO');
    insert into logs (id, log_tags) values (34,'</Event>');
    
    insert into logs (id, log_tags) values (35,'<Event time="Sat Apr 15 1:13:17.743" type="Debug" thread="2fec: Main CIM worker thread" elapsed="1" ');
    insert into logs (id, log_tags) values (36,null);
    insert into logs (id, log_tags) values (37,'</Event>');
end;
/
1 rows affected

select --Step #1:  (https://stackoverflow.com/a/76021834/5576771)
       sum(case when log_tags like '<Event%' then 1 else 0 end) over (order by id) group_id,
       --Step #2:
       case 
           when substr(log_tags,1,13) = '<Event time="'                      then 1
           when substr(log_tags,1, 9) = 'Database:'                          then 2
           when substr(log_tags,1,10) = '      SQL:'                         then 3
           when substr(log_tags,1,34) = '      Number of features returned:' then 4
           when substr(log_tags,1, 8) = '</Event>'                           then 5
       end as type,    
       substr(log_tags,1,100) as log_tags
  from logs
 where log_tags is not null


GROUP_ID
TYPE
LOG_TAGS


1
1
<Event time="Sat Apr 15 1:13:17.750" type="Debug" thread="2fec: Main CIM worker thread" elapsed="0"

1
2
Database: C:\2023 Files\GDBs_4\New Mobile Geodatabase.geodatabase

1
3
      SQL: SELECT main.ACTIVE_TRANSPORTATION.OBJECTID,main.active_transportation_nt_flag_vw.flag FRO

1
5
</Event>

2
1
<Event time="Sat Apr 15 1:13:17.749" type="Debug" thread="2fec: Main CIM worker thread" elapsed="1"

2
5
</Event>

3
1
<Event time="Sat Apr 15 1:13:17.749" type="Debug" thread="2fec: Main CIM worker thread" elapsed="2"

3
2
Database: C:\2023 Files\GDBs_4\New Mobile Geodatabase.geodatabase

3
3
      SQL: SELECT main.ACTIVE_TRANSPORTATION.OBJECTID,main.active_transportation_nt_flag_vw.flag FRO

3
4
      Number of features returned: 100

3
5
</Event>

4
1
<Event time="Sat Apr 15 1:13:17.749" type="Debug" thread="2fec: Main CIM worker thread" elapsed="0"

4
5
</Event>

5
1
<Event time="Sat Apr 15 1:13:17.747" type="Debug" thread="2fec: Main CIM worker thread" elapsed="0"

5
2
Database: C:\2023 Files\GDBs_4\New Mobile Geodatabase.geodatabase

5
3
      SQL: SELECT main.ACTIVE_TRANSPORTATION.OBJECTID,main.active_transportation_nt_flag_vw.flag FRO

5
5
</Event>

6
1
<Event time="Sat Apr 15 1:13:17.747" type="Debug" thread="2fec: Main CIM worker thread" elapsed="2"

6
2
Database: C:\2023 Files\GDBs_4\New Mobile Geodatabase.geodatabase

6
3
      SQL: SELECT main.ACTIVE_TRANSPORTATION.OBJECTID,main.active_transportation_nt_flag_vw.flag FRO

6
4
      Number of features returned: 100

6
5
</Event>

7
1
<Event time="Sat Apr 15 1:13:17.746" type="Debug" thread="2fec: Main CIM worker thread" elapsed="1"

7
5
</Event>

8
1
<Event time="Sat Apr 15 1:13:17.746" type="Debug" thread="2fec: Main CIM worker thread" elapsed="0"

8
5
</Event>

9
1
<Event time="Sat Apr 15 1:13:17.744" type="Debug" thread="2fec: Main CIM worker thread" elapsed="0"

9
2
Database: C:\2023 Files\GDBs_4\New Mobile Geodatabase.geodatabase

9
3
      SQL: SELECT main.ACTIVE_TRANSPORTATION.OBJECTID,main.active_transportation_nt_flag_vw.flag FRO

9
5
</Event>

10
1
<Event time="Sat Apr 15 1:13:17.743" type="Debug" thread="2fec: Main CIM worker thread" elapsed="1"

10
5
</Event>


select * from 
(select --Step #1:  (https://stackoverflow.com/a/76021834/5576771)
       sum(case when log_tags like '<Event%' then 1 else 0 end) over (order by id) group_id,
       --Step #2:
       case 
           when substr(log_tags,1,13) = '<Event time="'                      then 'a1'
           when substr(log_tags,1, 9) = 'Database:'                          then 'a2'
           when substr(log_tags,1,10) = '      SQL:'                         then 'a3'
           when substr(log_tags,1,34) = '      Number of features returned:' then 'a4'
           when substr(log_tags,1, 8) = '</Event>'                           then 'a5'
       end as type,    
       substr(log_tags,1,100) as log_tags
  from logs
 where log_tags is not null)
PIVOT(
    max(log_tags)
    FOR type
    IN ( 
        'a1','a2','a3','a4','a5'
    )
)
ORDER BY group_id




GROUP_ID
'a1'
'a2'
'a3'
'a4'
'a5'


1
<Event time="Sat Apr 15 1:13:17.750" type="Debug" thread="2fec: Main CIM worker thread" elapsed="0"
Database: C:\2023 Files\GDBs_4\New Mobile Geodatabase.geodatabase
      SQL: SELECT main.ACTIVE_TRANSPORTATION.OBJECTID,main.active_transportation_nt_flag_vw.flag FRO
null
</Event>

2
<Event time="Sat Apr 15 1:13:17.749" type="Debug" thread="2fec: Main CIM worker thread" elapsed="1"
null
null
null
</Event>

3
<Event time="Sat Apr 15 1:13:17.749" type="Debug" thread="2fec: Main CIM worker thread" elapsed="2"
Database: C:\2023 Files\GDBs_4\New Mobile Geodatabase.geodatabase
      SQL: SELECT main.ACTIVE_TRANSPORTATION.OBJECTID,main.active_transportation_nt_flag_vw.flag FRO
      Number of features returned: 100
</Event>

4
<Event time="Sat Apr 15 1:13:17.749" type="Debug" thread="2fec: Main CIM worker thread" elapsed="0"
null
null
null
</Event>

5
<Event time="Sat Apr 15 1:13:17.747" type="Debug" thread="2fec: Main CIM worker thread" elapsed="0"
Database: C:\2023 Files\GDBs_4\New Mobile Geodatabase.geodatabase
      SQL: SELECT main.ACTIVE_TRANSPORTATION.OBJECTID,main.active_transportation_nt_flag_vw.flag FRO
null
</Event>

6
<Event time="Sat Apr 15 1:13:17.747" type="Debug" thread="2fec: Main CIM worker thread" elapsed="2"
Database: C:\2023 Files\GDBs_4\New Mobile Geodatabase.geodatabase
      SQL: SELECT main.ACTIVE_TRANSPORTATION.OBJECTID,main.active_transportation_nt_flag_vw.flag FRO
      Number of features returned: 100
</Event>

7
<Event time="Sat Apr 15 1:13:17.746" type="Debug" thread="2fec: Main CIM worker thread" elapsed="1"
null
null
null
</Event>

8
<Event time="Sat Apr 15 1:13:17.746" type="Debug" thread="2fec: Main CIM worker thread" elapsed="0"
null
null
null
</Event>

9
<Event time="Sat Apr 15 1:13:17.744" type="Debug" thread="2fec: Main CIM worker thread" elapsed="0"
Database: C:\2023 Files\GDBs_4\New Mobile Geodatabase.geodatabase
      SQL: SELECT main.ACTIVE_TRANSPORTATION.OBJECTID,main.active_transportation_nt_flag_vw.flag FRO
null
</Event>

10
<Event time="Sat Apr 15 1:13:17.743" type="Debug" thread="2fec: Main CIM worker thread" elapsed="1"
null
null
null
</Event>

GROUP_ID	TYPE	LOG_TAGS
1	1	<Event time="Sat Apr 15 1:13:17.750" type="Debug" thread="2fec: Main CIM worker thread" elapsed="0"
1	2	Database: C:\2023 Files\GDBs_4\New Mobile Geodatabase.geodatabase
1	3	SQL: SELECT main.ACTIVE_TRANSPORTATION.OBJECTID,main.active_transportation_nt_flag_vw.flag FRO
1	5	</Event>
2	1	<Event time="Sat Apr 15 1:13:17.749" type="Debug" thread="2fec: Main CIM worker thread" elapsed="1"
2	5	</Event>
3	1	<Event time="Sat Apr 15 1:13:17.749" type="Debug" thread="2fec: Main CIM worker thread" elapsed="2"
3	2	Database: C:\2023 Files\GDBs_4\New Mobile Geodatabase.geodatabase
3	3	SQL: SELECT main.ACTIVE_TRANSPORTATION.OBJECTID,main.active_transportation_nt_flag_vw.flag FRO
3	4	Number of features returned: 100
3	5	</Event>
4	1	<Event time="Sat Apr 15 1:13:17.749" type="Debug" thread="2fec: Main CIM worker thread" elapsed="0"
4	5	</Event>
5	1	<Event time="Sat Apr 15 1:13:17.747" type="Debug" thread="2fec: Main CIM worker thread" elapsed="0"
5	2	Database: C:\2023 Files\GDBs_4\New Mobile Geodatabase.geodatabase
5	3	SQL: SELECT main.ACTIVE_TRANSPORTATION.OBJECTID,main.active_transportation_nt_flag_vw.flag FRO
5	5	</Event>
6	1	<Event time="Sat Apr 15 1:13:17.747" type="Debug" thread="2fec: Main CIM worker thread" elapsed="2"
6	2	Database: C:\2023 Files\GDBs_4\New Mobile Geodatabase.geodatabase
6	3	SQL: SELECT main.ACTIVE_TRANSPORTATION.OBJECTID,main.active_transportation_nt_flag_vw.flag FRO
6	4	Number of features returned: 100
6	5	</Event>
7	1	<Event time="Sat Apr 15 1:13:17.746" type="Debug" thread="2fec: Main CIM worker thread" elapsed="1"
7	5	</Event>
8	1	<Event time="Sat Apr 15 1:13:17.746" type="Debug" thread="2fec: Main CIM worker thread" elapsed="0"
8	5	</Event>
9	1	<Event time="Sat Apr 15 1:13:17.744" type="Debug" thread="2fec: Main CIM worker thread" elapsed="0"
9	2	Database: C:\2023 Files\GDBs_4\New Mobile Geodatabase.geodatabase
9	3	SQL: SELECT main.ACTIVE_TRANSPORTATION.OBJECTID,main.active_transportation_nt_flag_vw.flag FRO
9	5	</Event>
10	1	<Event time="Sat Apr 15 1:13:17.743" type="Debug" thread="2fec: Main CIM worker thread" elapsed="1"
10	5	</Event>