Split Records in Table using SQL

Question

I have an table with the below records

PREFIX	BEGIN	END	DIV	INDUSTRY
1AB	00001	99999	LPSD	RI
1AB	00527	00528	MHUG	RI

I want to split it as below

PREFIX	BEGIN	END	DIV	INDUSTRY
1AB	00001	99999	LPSD	RI
1AB	00527	00528	MHUG	RI
1AB	00529	99999	LPSD	RI

If you see as because we have a record with the same prefix but begin and end are different, i want to split the first record into 2 records, one from 00001 to 00526 and 00529 to 99999.

Is it possible to perform the same using the SQL query

Answer 1

I think this works (but it would need a bigger data set to test it properly):

SELECT prefix,
       "BEGIN",
       "END",
       CASE type
       WHEN 1
       THEN div
       ELSE LAG(CASE type WHEN 1 THEN div END) IGNORE NULLS
              OVER (PARTITION BY prefix, industry, grp ORDER BY "BEGIN")
       END AS div,
       industry
FROM   (
  SELECT prefix,
         div,
         industry,
         value AS "BEGIN",
         type,
         SUM(type) OVER (PARTITION BY prefix, industry ORDER BY value) AS grp,
         LEAD(value) OVER (PARTITION BY prefix, industry ORDER BY value) AS "END"
  FROM   table_name
  UNPIVOT (value FOR type IN ("BEGIN" AS 1, "END" AS -1))
)
WHERE  grp > 0
ORDER BY prefix, industry, "BEGIN";

Which, for the sample data:

CREATE TABLE table_name (prefix, "BEGIN", "END", div, industry) AS
SELECT '1AB', '00001', '99999', 'LPSD', 'RI' FROM DUAL UNION ALL
SELECT '1AB', '00527', '00528', 'MHUG', 'RI' FROM DUAL;

Outputs:

PREFIX	BEGIN	END	DIV	INDUSTRY
1AB	00001	00527	LPSD	RI
1AB	00527	00528	MHUG	RI
1AB	00528	99999	LPSD	RI

db<>fiddle here