1

Trying to find a simple way to insert some repeating values in two columns in my table, something similar to the rep function in R-

for instance, I need to insert two values (chocolate and vanilla, 4 times each) and I need to insert 4 types of values that repeat twice such as -- 

flavor_type schedule_type
chocolate   weekly
chocolate   monthly
chocolate   quarterly
chocolate   yearly
vanilla     weekly
vanilla     monthly
vanilla     quarterly
vanilla     yearly
Kirill Polishchuk
  • 54,804
  • 11
  • 122
  • 125
S31
  • 904
  • 2
  • 9
  • 21

1 Answers1

4

You can use cross join:

select *
from (values('chocolate'), ('vanilla')) flavor(flavor_type)
cross join (values('weekly'), ('monthly'), ('quarterly'), ('yearly')) schedule(schedule_type)

Output:

flavor_type schedule_type
----------- -------------
chocolate   weekly
chocolate   monthly
chocolate   quarterly
chocolate   yearly
vanilla     weekly
vanilla     monthly
vanilla     quarterly
vanilla     yearly
Kirill Polishchuk
  • 54,804
  • 11
  • 122
  • 125
  • Hm, this seems to give me a table with a total of 5 columns and 2 rows: * one column for flavor type with no repetition - just two distinct values * four columns for each respective schedule type, repeated for flavor – S31 Jul 07 '19 at 23:37
  • @S31, this query produces a resultset with two columns and eight rows, pls see answer. – Kirill Polishchuk Jul 07 '19 at 23:40