Concatenate rows in a column into a comma delimited string without c() and without \n but with single quotes on each item for SQL use

Question

Here's what I have:

# create fake data in representative format
data <- data.frame(test = c('a2','t33b','s5c','d102','e4e','df1f'))
data <- as_tibble(data)

Current code:

data2 <- data %>% summarise(test2 = toString(test)) %>% ungroup()

Outputs:

# A tibble: 1 x 1
  test2                        
  <chr>                        
1 a2, t33b, s5c, d102, e4e, df1f

But how can I get the item in each of these 'words' of the string to have single quotes around them like this?:

Desired output:

  test2                        
  <chr>                        
1 'a2', 't33b', 's5c', 'd102', 'e4e', 'df1f'

The reason is I want to make a list of quoted strings for a SQL query using paste0 later. Thank you!

Similar questions:

In R, print vector in single quotes and comma separated

Collapse / concatenate / aggregate a column to a single comma separated string within each group

score 1 · Answer 1 · answered Oct 26 '22 at 21:57

We can just insert the single quotes with sub.

library(tidyverse)
data <- tibble(test = c('a2','t33b','s5c','d102','e4e','df1f'))

data |>
  summarise(test = paste(sub("(^.*$)", "'\\1'", test), collapse = ", ")) 
#> # A tibble: 1 x 1
#>   test                                      
#>   <chr>                                     
#> 1 'a2', 't33b', 's5c', 'd102', 'e4e', 'df1f'

Concatenate rows in a column into a comma delimited string without c() and without \n but with single quotes on each item for SQL use

1 Answers1