How to make R-code from SQL Server database?

Question

In SQL Server and Microsoft SQL Server Management Studio, how can I read data from database and make R-code from it? For example, if I have data

f1 f2
1 2
3 4
5 6

How can I make a query to return

f1=c(1,3,5)
f2=c(2,4,6)

Answer 1

Something like this. Note the ORDER BY is mandatory and you must specify order by columns that produce a complete ordering of the rows (ie a key), otherwise SQL is free to generate the column vectors with different orders.

So assuming f1 is a unique column in the table and so ordering by f1 produces a consistent, complete ordering of the rows:

declare @t table(f1 int, f2 int)
insert into @t values (1,2),(3,4),(5,6)

declare @rcode nvarchar(max) = 
concat(
'f1=c(', STUFF( (SELECT concat(',', f1)
            FROM @t
            ORDER BY f1
            FOR XML PATH('')),1, 1, ''),')
f2=c(',  STUFF( (SELECT concat(',', f2)
            FROM @t
            ORDER BY f1
            FOR XML PATH('')),1, 1, ''),')'
        )

select @rcode

outputs

f1=c(1,3,5)
f2=c(2,4,6)

Answer 2

Try this. A self-container example as you didn't specify your table name;

create table #data
    (
    f1 int,
    f2 int
    )

insert into #data
select 1,2
union
select 3,4
union
select 5,6

select top 1
    F1=case when isnull(Attribute,'')<>'' then left(Attribute,len(Attribute)-1) else '' end,
    F2=case when isnull(Attribute2,'')<>'' then left(Attribute2,len(Attribute2)-1) else '' end
from 
    #data
cross apply 
   (
    select distinct
          CAST(f1 as varchar(10)) + ','
    from 
        #data
    FOR XML PATH('') 
   ) cleaned (Attribute)
cross apply 
   (
    select distinct
          CAST(f2 as varchar(10)) + ','
    from 
        #data
    FOR XML PATH('') 
   ) cleaned2 (Attribute2)

begin try
    drop table #data
end try
begin catch
end catch