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