Split one row into multiple rows

witold_zawieja
Contributor

Hi

I'm new in Exasol and I'm strugling with following problem:

I want to create a VIEW to show multiple rows for each source row.
example:
I have table of events. I have to split each event longer than 1 day to many rows representing each day of this event.

source table:

event_nameStart_timeend_timeduration
event101.07.202103.07.20213
event205.07.202106.07.20212

 

expectet result:

event_nameStart_timedays
event101.07.20211
event102.07.20211
event103.07.20211
event205.07.20211
event206.07.20211

 

I'm looking forward for your advices

/Witold

1 ACCEPTED SOLUTION

Accepted Solutions

mwellbro
Xpert

Hi @witold_zawieja ,

maybe like this ?

with base(event_name,	Start_time,	end_time,	duration)
as (
SELECT 'event1',to_date('01.07.2021','DD.MM.YYYY'),to_date('03.07.2021','DD.MM.YYYY'),3 from dual union all
SELECT 'event2',to_date('05.07.2021','DD.MM.YYYY'),to_date('06.07.2021','DD.MM.YYYY'),2 from dual
),
dim_date as (select to_date('20210101','YYYYMMDD') + (level-1) as dat from dual connect by level<=365)
select * from base inner join dim_date on dat between start_time and end_time;

mwellbro_0-1626425890886.png

 

View solution in original post

6 REPLIES 6

mwellbro
Xpert

Hi @witold_zawieja ,

maybe like this ?

with base(event_name,	Start_time,	end_time,	duration)
as (
SELECT 'event1',to_date('01.07.2021','DD.MM.YYYY'),to_date('03.07.2021','DD.MM.YYYY'),3 from dual union all
SELECT 'event2',to_date('05.07.2021','DD.MM.YYYY'),to_date('06.07.2021','DD.MM.YYYY'),2 from dual
),
dim_date as (select to_date('20210101','YYYYMMDD') + (level-1) as dat from dual connect by level<=365)
select * from base inner join dim_date on dat between start_time and end_time;

mwellbro_0-1626425890886.png

 

View solution in original post

witold_zawieja
Contributor

hi @mwellbro 

so simply :). Thank you very much. It is very helpful for me.

mwellbro
Xpert

My pleasure 🙂

Two little side notes:

  1. "connect by level <= x " does tend to degrade performance once your "x" gets sufficiently large, using a comparable cross join to generate rows will be faster in that case 
  2. the syntax I used is a bit exa-specific , you could improve on that by using an actual table for the date join ( or the "cross join construct" mentioned in point 1. ) - not that there would be anything wrong with using exa-specific syntax on an exa 😉

exa-Tobias
Team Exasol
Team Exasol

That would be a very nice case for the new value range syntax we introduce in 7.1 (replacing the connect by level)

PeterK
Xpert

Speaking of connect-by, it looks like this could be solved using only a connect-by and not even bothering with the cross-join:

WITH base (event_name, start_time, end_time, duration) AS ( VALUES
    ( 'event1', TO_DATE( '01.07.2021', 'DD.MM.YYYY' ), TO_DATE( '03.07.2021', 'DD.MM.YYYY' ), 3 ),
    ( 'event2', TO_DATE( '05.07.2021', 'DD.MM.YYYY' ), TO_DATE( '06.07.2021', 'DD.MM.YYYY' ), 2 )
)
SELECT base.*, ADD_DAYS( start_time, LEVEL - 1 ) AS dat
FROM base
CONNECT BY PRIOR event_name = event_name 
       AND LEVEL <= DAYS_BETWEEN(end_time, start_time) + 1
ORDER BY event_name, LEVEL
;

 

 

mwellbro
Xpert

@PeterK : true, but I think once you reach enough volume in the CTE ( or what actually probably is Witolds table ) I´d favor a JOIN over CONNECT BY any day 😉
Also, it kind of feels more readable formulating "this-date BETWEEN that-date AND that-other-date" than to wrap my head around the LEVEL<=DAY_BETWEEN(...)+1, at least for me - but it´s definitely an interesting approach, thanks for sharing !