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

WITH CTE AS (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)
SELECT
group_id
, MAX(CASE WHEN  type  = 1 then log_tags END ) type1
, MAX(CASE WHEN  type  = 2 then log_tags END ) type2
, MAX(CASE WHEN  type  = 3 then log_tags END ) type3
, MAX(CASE WHEN  type  = 4 then log_tags END ) type4
, MAX(CASE WHEN  type  = 5 then log_tags END ) type5
FROM CTE
GROUP BY group_id
ORDER BY group_id


GROUP_ID
TYPE1
TYPE2
TYPE3
TYPE4
TYPE5


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	TYPE1	TYPE2	TYPE3	TYPE4	TYPE5
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>