CODEWITHSUNDEEP
sqloracle-database
Bigjo
Bigjo

Reputation: 633

expand a row in different rows

I am working on a sql oracle database. I have a table with an id, begindate and enddate.

For example:

employee | begindate  |  enddate
john     | 18/02/2015 |  18/02/2015
john     | 19/02/2015 |  21/02/2015

I want to do a select statement of that table, but when the begindate is not equal to the enddate, it have to add some rows. In the example above the first line will stay like that, but the second line has to be expanded in three rows. the result of the select statement has to be:

john | 18/02/2015 | 18/02/2015
john | 19/02/2015 | 19/02/2015
john | 20/02/2015 | 20/02/2015
john | 21/02/2015 | 21/02/2015

So my select statement would have in this example 4 rows in total.

Has someone an idea how I can do that?

Upvotes: 0

Views: 264

Answers (3)

Boneist
Boneist

Reputation: 23588

Here's an alternative answer using connect by directly on the table:

with test as (select 'john' employee, to_date('18/02/2015', 'dd/mm/yyyy') begindate, to_date('18/02/2015', 'dd/mm/yyyy') enddate from dual union all
              select 'john' employee, to_date('19/02/2015', 'dd/mm/yyyy') begindate, to_date('21/02/2015', 'dd/mm/yyyy') enddate from dual)
select employee,
       begindate + level - 1 begindate,
       begindate + level - 1 enddate
from   test
connect by prior employee = employee
           and prior begindate = begindate
           and prior sys_guid() is not null
           and begindate + level - 1 <= enddate;

EMPLOYEE BEGINDATE  ENDDATE   
-------- ---------- ----------
john     18/02/2015 18/02/2015
john     19/02/2015 19/02/2015
john     20/02/2015 20/02/2015
john     21/02/2015 21/02/2015

I would recommend you test the answers which work and see which one has the best performance for your data.

Upvotes: 1

Y.B.
Y.B.

Reputation: 3586

The solution would be very similar to ORACLE SQL:Get all integers between two numbers with "two numbers" being the date span between begindate and enddate.

Something like:

Create Table test (employee varchar(50), begindate date, enddate date);

Insert Into test (employee, begindate, enddate)
Values ('john', TO_DATE('18/02/2015', 'dd/mm/yyyy'), TO_DATE('18/02/2015', 'dd/mm/yyyy'));

Insert Into test (employee, begindate, enddate)
Values ('john', TO_DATE('19/02/2015', 'dd/mm/yyyy'), TO_DATE('21/02/2015', 'dd/mm/yyyy'));

And then

Select employee, enumdate As begindate, enumdate As enddate
From test Cross Apply (
    Select begindate + rownum - 1 As enumdate
    From Dual
    Connect By Level <= enddate - begindate + 1
) enum;

Upvotes: 0

MT0
MT0

Reputation: 168061

Oracle Setup:

CREATE TABLE employees ( employee, begindate, enddate ) AS
SELECT 'john', DATE '2015-02-18', DATE '2015-02-18' FROM DUAL UNION ALL
SELECT 'john', DATE '2015-02-19', DATE '2015-02-21' FROM DUAL;

Query:

SELECT e.employee,
       t.COLUMN_VALUE AS begindate,
       t.COLUMN_VALUE AS enddate
FROM   employees e,
       TABLE(
         CAST(
           MULTISET(
             SELECT e.begindate + LEVEL - 1
             FROM   DUAL
             CONNECT BY LEVEL <= e.enddate - e.begindate + 1
           )
           AS SYS.ODCIDATELIST
         )
       ) t;

Results:

EMPLOYEE BEGINDATE ENDDATE 
-------- --------- ---------
john     18-FEB-15 18-FEB-15 
john     19-FEB-15 19-FEB-15 
john     20-FEB-15 20-FEB-15 
john     21-FEB-15 21-FEB-15

Upvotes: 3

Related Questions