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

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) ? — Georgina Skibinski
– Georgina Skibinski, Commented Sep 2, 2019 at 8:21
Have a help table (or recursive cte) that returns all possible years. JOIN. — jarlh
– jarlh, Commented Sep 2, 2019 at 8: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
– Ed Bangga, Commented Sep 2, 2019 at 8:38
A dialect-agnostic answer might prove difficult because of the fact that date functions are highly vendor specific. — Zohar Peled
– Zohar Peled, Commented Sep 2, 2019 at 8:44

GMB · Accepted Answer · 2019-09-02 09:01:31Z

5

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

edited Sep 2, 2019 at 9:01

answered Sep 2, 2019 at 8:48

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Zohar Peled Over a year ago

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.

GMB Over a year ago

@ZoharPeled: yes the VALUES() syntax is much shorter here, I updated my answer accordingly. Thanks !

Ed Bangga · Accepted Answer · 2019-09-02 09:00:12Z

2

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:

answered Sep 2, 2019 at 9:00

Ed Bangga

13k4 gold badges18 silver badges31 bronze badges

Comments

marc_s · Accepted Answer · 2019-09-10 04:59:31Z

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;

Collectives™ on Stack Overflow

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

3 Answers 3

2 Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Linked

Related