My original query:

SELECT desc, start_date
FROM foo.bar
WHERE desc LIKE 'Fall%' AND desc NOT LIKE '%Med%'
UNION
SELECT desc, end_date
FROM foo.bar
WHERE desc LIKE 'Spring%' AND desc NOT LIKE '%Med%'
ORDER BY start_date;

With this query, I get (roughly) the data set I am looking for. I now need to take that data and combine the results taking two at a time in order and then produce a result like:

Where DESC is a concatenation of the DESC form row 1 and 2, START_DATE is the date from row 1 and END_DATE is the date from row 2. Following this same pattern for the entire data set.

Any help with a query that will produce the result I need is greatly appreciated. Not sure if I'm heading down the right path or if that originally query is just wrong.

As stated above, I tried the supplied query, which gives me the data I need. However, I've been unsuccessful in finding a way to format it into my desired output. It should also be noted that I am running this on an Oracle database.

CodePudding user response：

Instead of union, use each of those queries as CTEs (with a slight modification - include row number you'll later use in JOIN):

Sample data:

SQL> with test (description, datum) as
  2    (select 'Fall 1971'  , date '1971-08-15' from dual union all
  3     select 'Spring 1972', date '1972-05-15' from dual union all
  4     select 'Fall 1972'  , date '1972-08-15' from dual union all
  5     select 'Spring 1973', date '1973-05-15' from dual union all
  6     select 'Fall 1973'  , date '1973-08-15' from dual union all
  7     select 'Spring 1974', date '1974-05-15' from dual union all
  8     select 'Fall 1974'  , date '1974-08-15' from dual union all
  9     select 'Spring 1975', date '1975-05-15' from dual
 10    ),

Query begins here: t_start and t_end represent your current queries

 11  t_start as
 12    (select description, datum,
 13       row_number() Over (order by datum) rn
 14     from test
 15     where description like 'Fall%' and description not like '%Med%'
 16    ),
 17  t_end as
 18    (select description, datum,
 19       row_number() Over (order by datum) rn
 20     from test
 21     where description like 'Spring%' and description not like '%Med%'
 22    )

Finally:

 23  select s.description ||' - '|| e.description as description,
 24    s.datum start_date,
 25    e.datum end_date
 26  from t_start s join t_end e on s.rn = e.rn
 27  order by s.rn;

DESCRIPTION               START_DAT END_DATE
------------------------- --------- ---------
Fall 1971 - Spring 1972   15-AUG-71 15-MAY-72
Fall 1972 - Spring 1973   15-AUG-72 15-MAY-73
Fall 1973 - Spring 1974   15-AUG-73 15-MAY-74
Fall 1974 - Spring 1975   15-AUG-74 15-MAY-75

SQL>

CodePudding user response：

You can also use the MODEL clause to avoid to scan the table twice:

with data(description,datum) as (
    select 'Fall 1971'  , date '1971-08-15' from dual union all
    select 'Spring 1972', date '1972-05-15' from dual union all
    select 'Fall 1972'  , date '1972-08-15' from dual union all
    select 'Spring 1973', date '1973-05-15' from dual union all
    select 'Fall 1973'  , date '1973-08-15' from dual union all
    select 'Spring 1974', date '1974-05-15' from dual union all
    select 'Fall 1974'  , date '1974-08-15' from dual union all
    select 'Spring 1975', date '1975-05-15' from dual
)
select description, start_date, end_date
from (
    select rn, desc1 as description, start_date, end_date
    from (
        select row_number() over(order by datum) as rn, description, datum
        from data
        where description not like '%Med%'
    )
    model 
        dimension by (rn)
        measures (
            cast(' ' as varchar2(256)) as desc1, description, cast(NULL as DATE) start_date, cast(NULL as DATE) end_date , datum
        )
        rules (
            desc1[mod(rn,2)=1] = description[cv()] || ' - ' || description[cv() 1],
            start_date[mod(rn,2)=1] = datum[cv()], 
            end_date[mod(rn,2)=1] = datum[cv() 1] 
        )
)
where mod(rn,2)=1
;