Fill in rows per group for pivoting

Question

I have an Oracle 18c table called LOGS:

create table logs (id number, log_tags varchar2(4000));

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>');

db<>fiddle

My end goal is to pivot the data. Each group of log_tags would be pivoted into 5 columns.

Like this:

As a novice, I think a few steps are required:

Solved: Assign GROUP_IDs to rows based on start/end tags
Solved: Categorize each tag per group using a TYPE column.
Unsolved: Fill in the gaps. Each group should have 5 rows, one for each type.
Unsolved: Pivot each group of tags into 5 type columns.
Unsolved: Parse the pivoted type columns into additional columns. (Edit: No longer needed.)

Steps #1 and #2 (solved):

select --Step #1:
       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>

Question:

How can I solve Step #3 - Fill in the gaps, so that each group has 5 rows, one for each type?

Like this (*):

  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         *4
         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         *2
         2         *3
         2         *4
         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         *2
         4         *3
         4         *4
         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         *4
         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         *2
         7         *3
         7         *4
         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         *2
         8         *3
         8         *4
         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         *4
         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         *2
        10         *3
        10         *4
        10          5 </Event>

score 1 · Answer 1 · answered Apr 15 '23 at 21:18

This sis a simple pivpt as long as you know how many type there are.

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>

fiddle

Are you sure whole 10 rows of output are needed here? – markalex Apr 15 '23 at 21:25 — markalex, Apr 15 '23 at 21:25

markalex · Accepted Answer · 2023-04-15T21:31:31.273

It looks like all you need to accomplish all your steps three and four is just a pivot:

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

Fiddle here.

Regarding step 5: it is not entirely clear what exactly is you goal (and most likely it would be a good fit for separate question), but id all you need is just extract attributes of tag Event into separate columns, then look into XMLTABLE. There are a plenty of question on this matter here. Just as an example: here is one asked yesterday.

Kazi Mohammad Ali Nur Romel · Answer 3 · 2023-04-15T22:36:45.117

Thanks for asking such detail and resourceful question. You can use oracle pivot to solve rest of your problem.

Query:

 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 'Type_1'
           when substr(log_tags,1, 9) = 'Database:'                          then 'Type_2'
           when substr(log_tags,1,10) = '      SQL:'                         then 'Type_3'
           when substr(log_tags,1,34) = '      Number of features returned:' then 'Type_4'
           when substr(log_tags,1, 8) = '</Event>'                           then 'Type_5'
       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 ('Type_1','Type_2','Type_3','Type_4','Type_5')
 ) order by group_id

Output:

GROUP_ID	'Type_1'	'Type_2'	'Type_3'	'Type_4'	'Type_5'
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>

fiddle

Thanks. For my notes, this is how I can convert the column names that have single quotes into names that don't have quotes: `max(log_tags) for type in ('Type_1' as type_1,'Type_2' as type_2,'Type_3' as type_3,'Type_4' as type_4,'Type_5' as type_5)`. Source: [https://stackoverflow.com/questions/22103060/oracle-pivot-query-gives-columns-with-quotes-around-the-column-names-what]. — User1974, Apr 16 '23 at 00:49

User1974 · Answer 4 · 2023-04-17T05:13:49.907

This query uses the pivot technique from @markalex and @Kazi's answers. With a few small differences:

Trims some of the pivoted columns to remove leading spaces.
Removes the single-quotes from the pivoted column names.
Added a 6th type/column to handle values that aren't caught by types 2-4. See the comment in the SQL for more info.

select group_id, col_1, col_2, trim(col_3) as col_3, trim(col_4) as col_4, col_5, trim(col_6) as col_6 from 
   (select sum(case when log_tags like '<Event%' then 1 else 0 end) over (order by id) group_id,  --https://stackoverflow.com/a/76021834/5576771
           case 
             when substr(log_tags,1,13) = '<Event time="'                      then 'col_1'
             when substr(log_tags,1, 9) = 'Database:'                          then 'col_2'
             when substr(log_tags,1,10) = '      SQL:'                         then 'col_3'
             when substr(log_tags,1,34) = '      Number of features returned:' then 'col_4'
             when substr(log_tags,1, 8) = '</Event>'                           then 'col_5'
             else 'col_6' --Sometimes the values in cols 2-4 are different from what I'm querying for above. Col_6 is a catch-all for any unexpected values. 
                          --However, col_6 will only grab the first unexpected value it comes across; all others will be ignored. I haven't found a better way to do it.
           end as type,    
           log_tags  --substr(log_tags,1,100)
      from logs
     where log_tags is not null)
pivot (max(log_tags) for type in ('col_1' as col_1,'col_2' as col_2,'col_3' as col_3,'col_4' as col_4,'col_5' as col_5, 'col_6' as col_6)) 
order by group_id

Related: Copy Diagnostic Monitor logs as Excel table, not as vertical tags

Fill in rows per group for pivoting

4 Answers4

Linked