How to create single/multiple number ranges from continuous but broken small ranges in oracle

I have following data set in one table

main Product	Sub Product	Start Serial	End Serial
Main01	sub01	166255	166258
Main01	sub02	666255	666258
Main02	sub01	166259	166262
Main02	sub02	666259	666262
Main03	sub01	166267	166270
Main03	sub02	666267	666270

and need to create summarized ranges as below

Sub Product	Start Serial	End Serial
sub01	166255	166262
sub02	666255	666262
sub01	166267	166270
sub02	666267	666270

Solution

You can use MATCH_RECOGNIZE to perform row-by-row pattern matching:

SELECT main_product,
       sub_product,
       start_serial,
       end_serial
FROM   table_name
MATCH_RECOGNIZE(
  PARTITION BY sub_product
  ORDER BY start_serial
  MEASURES
    FIRST(main_product) AS main_product,
    FIRST(start_serial) AS start_serial,
    MAX(end_serial) AS end_serial
  PATTERN (overlaps* last_row)
  DEFINE
    overlaps AS MAX(end_serial) + 1 >= NEXT(start_serial)
)

Which, for the sample data:

CREATE TABLE table_name (Main_Product, Sub_Product, Start_Serial, End_Serial) AS
SELECT 'Main01', 'sub01', 166255, 166258 FROM DUAL UNION ALL
SELECT 'Main01', 'sub02', 666255, 666258 FROM DUAL UNION ALL
SELECT 'Main02', 'sub01', 166259, 166262 FROM DUAL UNION ALL
SELECT 'Main02', 'sub02', 666259, 666262 FROM DUAL UNION ALL
SELECT 'Main03', 'sub01', 166267, 166270 FROM DUAL UNION ALL
SELECT 'Main03', 'sub02', 666267, 666270 FROM DUAL;

Outputs:

MAIN_PRODUCT	SUB_PRODUCT	START_SERIAL	END_SERIAL
Main01	sub01	166255	166262
Main03	sub01	166267	166270
Main01	sub02	666255	666262
Main03	sub02	666267	666270

fiddle