2

I have a table that looks like this:

update_date  start_date   end_date   category_1 category_2    value
2013-03-04   2013-05-11  2013-06-08     a1           b1        23
2013-02-19   2013-03-16  2013-09-21     a2           b2       112
..

Now i want to transform the above table to this format:

date   category_1   category_2   value   update_date

For each pair of (category_1, category_2) I want to have a continuous series such as date is between start_date and end_date for each pair of (category_1, category_2) and keeping the respective value and update_date.

For example, for (a1, b1) I would have:

date       category_1  category_2   value   update_date
2013-05-11    a1          b1          23     2013-03-04
.             a1          b1          23     2013-03-04
.             a1          b1          23     2013-03-04
2013-06-08    a1          b1          23     2013-03-04

and this table would continue for each pair of (category_1, category_2).

I reckon there'd be duplicates of values in date column but i'm OK with that as long as there are no duplicates of (category_1, category_2) pair.

Anybody knows how to solve this?

Thank you in advance.

1
  • @ypercubeᵀᴹ - yes, i tried several approaches on using generate_series() but can't figure out how to generate_series per categories and then join them somehow.. – nick May 3 '17 at 16:54
3

You can use generate_series() function:

select
    d.d::date as date, 
    t.update_date,  
    t.category_1, 
    t.category_2,
    t.value
from 
    t, 
    generate_series(start_date, end_date, interval '1 day') as d(d) ;

It works basically by taking each row from t and producing a set of rows, one for each day from start_date up to (and including) end_date.

Test at dbfiddle.uk

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.