How to send varying number of variables in R function to be used by sql query

I’m writing a R code which will pull data from a data frame using sql query. My function looks like

formatData =function(compe1,compe2)
{
 query=paste("Select A.month as month,sales_comp1,salse_comp2 from (select  
 month,cast(dollar as real) as sales_comp1 from data where 
 product=",sep="","'",compe1,"') A join             ( select  
 month,cast(dollar as real) as sales_comp2 from data where 
 product='",compe2,"')B on A.month=B.month")
 dataComp12=sqldf(query) 
 return(dataComp12)
 }

Here I’m using 2 variables in function Now for some cases I will have multiple variables for the same sql query. How do I write my formatData function dynamically so that it can take variables dynamically Let’s assume in future I will have 3 variables. Then my query will be

query=paste("Select A.month as month,sales_comp1,sales_comp2,sales_comp3 
from (select  
month,cast(dollar as real) as sales_comp1 from data where 
product=",sep="","'",comp1,"') A join             ( select  
month,cast(dollar as real) as sales_comp2 from data where 
product='",comp2,"')B on A.month=B.month
        join
        ( select  
          month,cast(dollar as real) as sales_comp3 from data where 
          product='",comp2,"') C
        on A.month=c.month")
 dataComp=sqldf(query)

Similar way for more than 3 variables & my variable count can go to around 80

Answer

Questions to SO r tag should have a complete minimal reproducible example which the question did not include so this is untested but hopefully gives the basic idea.

comp <- c("comp1", "comp2", "comp3") # input

n <- length(comp)
s <- sprintf("s%d as (select month, cast(dollar as real) as sales_comp%d from data
  where product = '%s')n", 1:n, 1:n, comp)

sp <- sprintf("with %s select s1.month, %s nfrom (select * from s1 %s)",
  toString(s), 
  toString(paste0("sales_compe", 1:n)), 
  paste(sprintf("njoin s%d on s1.month = s%d.month", 2:n, 2:n), collapse = " "))

cat(sp)

giving:

with s1 as (select month, cast(dollar as real) as sales_comp1 from data
      where product = 'comp1')
, s2 as (select month, cast(dollar as real) as sales_comp2 from data
      where product = 'comp2')
, s3 as (select month, cast(dollar as real) as sales_comp3 from data
      where product = 'comp3')
 select s1.month, sales_compe1, sales_compe2, sales_compe3 
from (select * from s1 
join s2 on s1.month = s2.month 
join s3 on s1.month = s3.month)