r - Result out of SQL to list -

i have data set out of sql in csv format , table/data structure.

order id     items o1            beer o1            wine o2            beer o2            wine o3            beer o4            chips 

i need convert in r following format

o1 beer,wine o3 beer,chips  

any appreciated.

i tried following :

# mock data o1 <- c("beer","wine") o2 <- c("beer","wine") o3 <- c("beer","chips") o4 <-c("curd","chips") o5 <-c("beer") o6<-c("wine") o7 <-c("fruits") o8<- c("wine","cheese")  order <- list(o1,o2,o3,o4,o5,o6,o7,o8)  library(arules)  dt <- (order,"transactions") 

• output:

  str(order) list of 8  $ : chr [1:2] "beer" "wine"  $ : chr [1:2] "beer" "wine"  $ : chr [1:2] "beer" "chips"  $ : chr [1:2] "curd" "chips"  $ : chr "beer"  $ : chr "wine"  $ : chr "fruits"  $ : chr [1:2] "wine" "cheese"  str(dt)  formal class 'transactions' [package "arules"] 4 slots   ..@ transactioninfo:'data.frame': 0 obs. of  0 variables   ..@ data           :formal class 'ngcmatrix' [package "matrix"] 5                      

note: cannot change in sql because have 800k rows.

you can 1 thing. first table content posted has order id , items. can insert them work table, add third column in work table store dense_rank() of records, keep order id in group partition clause , order items. after loading table. can make use of statement. coalesce(max((case rank_col when 1 items end)),'')  || ',' || coalesce(max((case rank_col when 2 items end)),'')   || ',' || , on. has shortcoming, have know maximum number of items present across order.

in case have xml functions running fine in data base can use below clause ( overcome shortcoming of previous approach, need make sure command running fine )

xmlagg((items)|| ',' order rank_col) (varchar(50)) custom_concatenated_items . hope helps.

thanks aritra