Generating Multiple tables via for loop or apply function in sql query

Question

I am basically running a query to get IDs for a specific year given a criteria . For example

table.2001=dbGetquery(conn,"Select ID from base table where x > 3 and year = 2001")

Now here i want to iterate over the year, (2001 to 2015) and i get output table.2001, table.2002, table.2003 and so on for each year.

Use SQL IN for selected years, then use R function split to split the dataframe into list of dataframes based on "year" column. Or read about how to pass a string into SQL statement as a variable name — zx8754
– zx8754, Commented Mar 7, 2017 at 6:55
Do you literally want to create tables for every year or just store resultset in some variable? — Atul
– Atul, Commented Mar 7, 2017 at 6:59
@zx875 - Thanks for the reply - but here i can have one ID say A coming multiple times in the same year . I want distinct ID as long as it satisfies the X>3 once in that year . and that same ID , can occur in next year again . so if i do distinct with IN , i guess it'll count that ID only once in the my sheet. I am looking at data sets . table.2001 = IDs : a , b ,c ,f , table.2002 = IDs a , e , b table.2003 = b , f , d etc and so on — Learner_seeker
– Learner_seeker, Commented Mar 7, 2017 at 7:04
@Maverick - thanks for the reply. yes i want to create results sets for each year separately through one for loop or apply function with sql query in R with dbgetQuery — Learner_seeker
– Learner_seeker, Commented Mar 7, 2017 at 7:08

r.user.05apr · Accepted Answer · 2017-03-07 08:41:58Z

1

for (i in (2001:2003)) {
  assign(paste0("df",i),dbGetquery(conn,paste0("Select ID from base table where x > 3 and year = ",i)))
}

answered Mar 7, 2017 at 8:41

r.user.05apr

5,4783 gold badges27 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

@r.user.05.apr - Super sir ! it worked , this is what i was looking for !