SQL: How to 'split' rows in a table by the values of certain columns?

Question

I have a table with rows like this:

Name  | date_from  | date_to    | age
------+------------+------------+-----
Alice | 01.12.2004 | 03.04.2008 | 35
Bob   | 04.02.2013 | 04.11.2014 | 43

I would like to make a table that splits each row into one-year intervals by the date_from and date_to columns, keeping the Name, and updating the age, like this:

Name  | date_from  | date_to    | age
------+------------+------------+-----
Alice | 01.12.2004 | 01.12.2005 | 35
Alice | 01.12.2005 | 01.12.2006 | 36
Alice | 01.12.2006 | 01.12.2007 | 37
Alice | 01.12.2007 | 01.12.2008 | 38
Alice | 01.12.2008 | 03.04.2008 | 39
Bob   | 04.02.2013 | 04.02.2014 | 43
Bob   | 04.02.2014 | 04.11.2014 | 44

Is this possible to do in SQL?

What sql are you using (MS Sql, MySQL, Oracle, PostgreSQL) ? — Grzegorz Skibinski, Sep 02 '19 at 08:21
Have a help table (or recursive cte) that returns all possible years. JOIN. — jarlh, Sep 02 '19 at 08:36
seems there's a missing Alice | 01.12.2008 | 03.04.2008 | 38 (same for Bob), or Bob 2nd row should not be there — Ed Bangga, Sep 02 '19 at 08:38
@GrzegorzSkibinski: MS SQL, but if there exists a dialect-agnostic answer that's even better. — Ketil Tveiten, Sep 02 '19 at 08:41
@jarlh: would you mind elaborating that suggestion into a full answer? — Ketil Tveiten, Sep 02 '19 at 08:43
A dialect-agnostic answer might prove difficult because of the fact that date functions are highly vendor specific. — Zohar Peled, Sep 02 '19 at 08:44
I would try this one: https://stackoverflow.com/questions/17942508/sql-split-values-to-multiple-rows (you just need to find a way to get array of years between 2 dates) — Grzegorz Skibinski, Sep 02 '19 at 08:50

GMB · Accepted Answer · 2019-09-02T09:01:31.680

One solution would be to generate a list of numbers and join it with the original table, adding years to the starting date until the end date is reached.

The following query handles up to 5 years span (to support more years, you would need to extend the subquery with more VALUESs)

SELECT
    name, 
    DATEADD(year, x.n, t.date_from) date_from,
    CASE 
        WHEN DATEADD(year, x.n + 1, t.date_from) > t.date_to 
        THEN date_to 
        ELSE DATEADD(year, x.n + 1, t.date_from) 
    END date_to,
    t.age + x.n age
FROM 
    mytable t
    INNER JOIN (
        VALUES(0), (1), (2), (3), (4), (5)
    ) x(n) ON DATEADD(year, x.n, t.date_from) <= t.date_to
ORDER BY name, age

This demo on DB Fiddle with your sample data returns:

name  | date_from           | date_to             | age
:---- | :------------------ | :------------------ | --:
Alice | 01/12/2004 00:00:00 | 01/12/2005 00:00:00 |  35
Alice | 01/12/2005 00:00:00 | 01/12/2006 00:00:00 |  36
Alice | 01/12/2006 00:00:00 | 01/12/2007 00:00:00 |  37
Alice | 01/12/2007 00:00:00 | 03/04/2008 00:00:00 |  38
Bob   | 04/02/2013 00:00:00 | 04/02/2014 00:00:00 |  43
Bob   | 04/02/2014 00:00:00 | 04/11/2014 00:00:00 |  44

Better use an existing tally table, or you can use `...inner join (values(1),(2),(3),(4),(5),(6),(7)) x(n)...` instead of the `union all` query. Still an upvote from my end. — Zohar Peled, Sep 02 '19 at 08:57
@ZoharPeled: yes the `VALUES()` syntax is much shorter here, I updated my answer accordingly. Thanks ! — GMB, Sep 02 '19 at 09:02

score 1 · Answer 2 · answered Sep 02 '19 at 09:00

here's your query.

;with cte as (
    select 1 as ctr, DATEDIFF(year, cast(date_from as datetime), cast(date_to as datetime)) as ct
        ,cast(date_from as date) as dt, cast(date_from as date) as dt2, date_to, cast(age as int) as age, [name] from test
    union all
    select ctr +  1, ct, dateadd(year, 1, dt), dt2, date_to, age + 1, [name]  from cte
    where ctr + 1 <= ct+1)
    select [name], dt as date_from, case when ctr - 1 != ct then dt else date_to end as date_to, age from cte order by dt2, age

output:

score 0 · Answer 3 · edited Sep 10 '19 at 04:59

Another possible solution using SQL Server

-- data preparation
    create table test1
    (
        name varchar(20),
        date_from date,
        date_to date ,
        age int

    )



    insert into test values ('alice' , '01-2-2008' , '11-3-2014' , 35 )
    insert into test values ('bob' , '06-2-2005' , '7-10-2016' , 20)

    create table test2
    (
        name varchar(20),
        date_from date,
        date_to date ,
        age int

    )
    -- query
    declare @name varchar(20)
    declare @date_from date
    declare @date_to date
    declare @age int
    declare @date_step as date
    declare @sql_st as nvarchar(max)
    declare cur cursor for select  name, date_from , date_to , age from test
    open cur;
        fetch next from cur into @name , @date_from , @date_to , @age
        while @@FETCH_STATUS = 0
        begin
            set @date_step = dateadd(year,1,@date_from)
            while @date_to > @date_step
            begin
                set @sql_st = concat('insert into test2 values (''',@name , ''' , ''' , @date_from , ''' , ''',@date_step,''',',@age , ' )')
                print(@sql_st)
                exec sp_executesql @sql_st
                set @date_from = @date_step
                set @date_step = dateadd(year,1,@date_step)
                set @age = @age + 1
            end
            set @sql_st = concat('insert into test2 values (''',@name , ''' , ''' , @date_from , ''' , ''',@date_to,''',',@age , ' )')
            exec sp_executesql @sql_st
            --print(@sql_st)            
            fetch next from cur into @name , @date_from , @date_to , @age       
        end
    close cur;
    deallocate cur;

SQL: How to 'split' rows in a table by the values of certain columns?

3 Answers3