EDIT: I am using the library package DBI for connectivity and will switch to RODBC as per some comments here: DBI/Github/Issue comments
EDIT 2: RODBC package does not save the day returning error message:
[RODBC] ERROR: Could not SQLExecDirect
EDIT 3: Works fine with SQLacademy in Python.
Using SQL Server I am able to perform the following query without hassle
Outcomes AS ( SELECT Number, OutcomeDate, EndMatter, CAST(STUFF((SELECT ', ' + CAST(OutcomeName AS VARCHAR(MAX)) FROM Research.EndMatterOutcome a WHERE a.Number = b.Number FOR XML PATH('')),1,1,'') AS VARCHAR(MAX)) AS OutcomeName FROM Research.EndMatterOutcome_View b WHERE (b.OutcomeName IS NOT NULL OR CAST(b.OutcomeName AS VARCHAR(MAX)) <> '') GROUP BY Number, OutcomeDate, EndMatter )
Its just joinery to collapse two rows (one column) of values into one separated by a comma. When used as part of a larger query in an SQL chunk in R studio I get the following:
Error in result_fetch(res@ptr, n, ...) :
nanodbc/nanodbc.cpp:2836: 07009: [Microsoft][ODBC SQL Server Driver]Invalid Descriptor Index
I understand from StackOverflow that there are some workarounds concerning VARCHAR(MAX), such as using these in the final SELECT statement etc but these approaches are several years old, is not easy to build in with the much larger query that this is nested within and am hoping that there is another approach because as I say the query works fine via SQL Server Management Studio.