I have a table full of dates, the dates are unique but for each date I want to have numbers 1-3. So as it stands now all the dates are distinct but my objective is to have a new column where each date has 3 rows instead of one with the numbers 1-3.
Right now my table looks like this:
Date
1/1/2016
2/1/2016
What I want is this:
Date | Number
1/1/2016 | 1
1/1/2016 | 2
1/1/2016 | 3
2/1/2016 | 1
2/1/2016 | 2
2/1/2016 | 3
Is this possible with a query?
cross join with generate_series.
select d.dt,n.num
from dates_tbl d
cross join generate_series(1,3) n(num)
You could do this in a number of ways.
Here's one:
start by modify the table to add the additional number field. then create a table and insert the values 1, 2, 3 into the table as three different rows. If you only want to do this as a one-off you could do this as a temp table:
CREATE TEMPORARY TABLE temp_location
(
number int
)
ON COMMIT DROP;
INSERT into your table with the new field the results of a CROSS JOIN with the numbers table and your dates.
