0
377
ViewsDuplicate records (rows) with modified values (Postgresql)
I have a dataset that I am preparing for a pivot view (in Excel). It's customer data and I want to create a view that allows to summarize how many customers were active every month. Therefore I need to duplicate rows with a modified value for first_status_date. This is my data:
This is where I am trying to get: (The green rows are duplicated, and the cells in bold are modified.
Because I am adding a month on top of the value of the cell above, I am thinking of working with the lag function. But I am not familiar with duplicating rows. The id 123 and 356 needed 2 duplicated rows because there is a 2 month difference between last and first status date, the id 221 needs only 1 duplicated row, as there is a one month difference.
1 answers
Answer question0
You can use generate_series() to generate the new rows - a little tweaking is needed for the end date to ensure that we do get a row for the last month, regardless of the actual month day:
select
t.id,
t.first_status,
d.first_status_date,
t.last_status,
t.last_status_date
from mytable t
cross join lateral generate_series(
t.first_status_date,
date_trunc('month', t.last_status_date) + interval '1 month',
interval '1 month'
) d(first_status_date)