Control break type SQL against Oracle db

Question

I'm working with a strangely designed (let's call it suboptimal) Oracle database. One issue is that one of the columns contains 2 different types of text data: "header" data and "content" data. Kind of like this:

id	text
1	Header 1:
2	abc
3	def
4	Header 2:
5	ghi
6	jkl
7	mno
8	Header 3:
9	pqr

If possible, I need to construct SQL that essentially returns the "Header" rows as control breaks for the "content" rows to get these results:

Header 1: abc
Header 1: def
Header 2: ghi
Header 2: jkl
Header 2: mno
Header 3: pqr

The id values are sequential, but the content for each header can span an arbitrary number of rows. So the only clue to which "header" applies to each "content" row is that it's the previous Header seen (max id value where text like '%Header% < id of current row).

Answer 1

My take on the problem using match_recognize, which is perfect fit when analyzing sequences of rows.

with t(id, text) as (
  select 1, 'Header 3:' from dual union all
  select 2, 'abc'       from dual union all
  select 3, 'def'       from dual union all
  select 4, 'Header 1:' from dual union all
  select 5, 'ghi'       from dual union all
  select 6, 'jkl'       from dual union all
  select 7, 'mno'       from dual union all
  select 8, 'Header 2:' from dual union all
  select 9, 'pqr'       from dual 
)
select id, header, text
from t match_recognize (
  order by id
  measures
    header.text header
  all rows per match
  pattern ({- header -} data*)
  define
    data as text not like 'Header%'
);

I am getting amazed how often one can come up with a simple solution using match_recognize.

Answer 2

This is a variation on Aleksej's answer that works:

select header || ' ' || text
from (select t.*,
             lag(case when text like 'Header%' then text end ignore nulls) over (order by id) as header 
      from t
     ) t
where text not like 'Header%';

Answer 3

Another approach:

WITH cteTestdata(ID, TEXT) AS
       (SELECT 1,   'Header 1:' FROM DUAL UNION ALL
        SELECT 2,   'abc' FROM DUAL UNION ALL
        SELECT 3,   'def' FROM DUAL UNION ALL
        SELECT 4,   'Header 2:' FROM DUAL UNION ALL
        SELECT 5,   'ghi' FROM DUAL UNION ALL
        SELECT 6,   'jkl' FROM DUAL UNION ALL
        SELECT 7,   'mno' FROM DUAL UNION ALL
        SELECT 8,   'Header 3:' FROM DUAL UNION ALL
        SELECT 9,   'pqr' FROM DUAL),
     cteHeaders AS
       (SELECT ID, TEXT
          FROM cteTestdata
          WHERE TEXT LIKE 'Header%'
          ORDER BY ID),
     cteLines AS
       (SELECT ID, TEXT
          FROM cteTestdata
          WHERE TEXT NOT LIKE 'Header%'
          ORDER BY ID)
SELECT h.TEXT || ' ' || l.TEXT AS COMBINED_TEXT
  FROM cteLines l
  INNER JOIN cteHeaders h
    ON h.ID = (SELECT MAX(h2.ID)
                 FROM cteHeaders h2
                 WHERE h2.ID < l.ID)
  ORDER BY l.ID

db<>fiddle here

Answer 4

You could try with something like this:

select header || text
from 
(
    select
        max (case when text like 'Header%' then text end) over (partition by 1 order by id) as header,
        id, text
    from yourTable
)
where text not like 'Header%'