collapse argument from the paste function is not supported

[PauloJhonny]

Hi,

I am querying a database in SQL Server using dbplyr (this package is amazing) and I would like to concatenate the strings of the column name, grouping by id and removing the duplicated names. For example:

id	name
1	paul
1	paul
1	john
2	lea
2	peter

to

id	names
1	paul, john
2	lea, peter

I tried to use the code below with the collapse argument, but it says it is not possible.

dt <- dt %>%
    dplyr::select(id, name) %>%
    dplyr::distinct() %>%
    dplyr::group_by(id) %>% 
    dplyr::mutate(names = paste(name, collapse = ", ")) %>%
    dplyr::collect()

Error: collapse not supported in DB translation of paste().

Is there any way I can do this using dbplyr?

Thank you.

[ianmcook]

Use str_flatten(name, collapse = ","). Does that work?

[PauloJhonny]

Hi, @ianmcook.

Unfortunately not.

Error: str_flatten() is not available in this SQL variant

[ianmcook]

It looks like you could implement the str_flatten() translation for SQL Server simply by copying the str_flatten() aggregate and window function translations for PostgreSQL and pasting them into the aggregate and window function translations for SQL Server.

@PauloJhonny are you interested in trying to create a pull request with this change?

[PauloJhonny]

@ianmcook Hi.

Yes. It would be really great if this function was translated. What I’m doing now is querying a stored procedure (which uses FOR XML PATH and STUFF) from SQL Server in R, but it’s so complicated for me.

Until so, I never created a pull request, so I'm not sure how to do it. What am I supposed to do?

And thank you for your help, @ianmcook!!

[ianmcook]

If you're comfortable with Git and the command line, you could follow these instructions:
Submitting a pull request to another repo

If you're more comfortable staying inside RStudio, then you can achieve the same thing with the usethis package. Instructions here:
Pull request helpers

[PauloJhonny]

Hi, @ianmcook. It's done. #406.

Thank you for your help. :)

[PauloJhonny]

@ianmcook, it seems that there are 3 Travis failures, but I think it's not related to the changes in the PR. What do you think about it?

-> https://travis-ci.org/github/tidyverse/dbplyr/builds/668496719?utm_source=github_status&utm_medium=notification

[ianmcook]

Yep, it looks like those test failures are caused by other problems that have nothing to do with your PR. dplyr and some related packages are undergoing major changes right now in preparation for the upcoming dplyr 1.0 release, and it's fairly common for some tests to be failing at times like that.

Later, one of the dbplyr maintainers might ask you to sync your fork so that all the changes that have been made in dbplyr in the meantime become part of your fork. Any changes that you push to your fork will automatically become part of the PR, and will trigger the Travis tests to run again. Don't delete your fork until the PR is merged.