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

How can I make a query to return

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

learn.microsoft.com/en-us/sql/advanced-analytics/tutorials/… — Bogdan Sahlean
– Bogdan Sahlean, Commented Aug 1, 2017 at 10:24
I have read that but I don't know how to put the R-code around the result and commas between the numbers. — Jaakko Seppälä
– Jaakko Seppälä, Commented Aug 1, 2017 at 11:08

David Browne - Microsoft · Accepted Answer · 2017-08-01 12:36:01Z

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)

answered Aug 1, 2017 at 12:36

David Browne - Microsoft

90.8k7 gold badges53 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user1529235 Over a year ago

concat assumes the version ? Stuff the way to go however.

David Browne - Microsoft Over a year ago

concat was introduced in SQL 2012, over five years ago. And it's significantly cleaner because it handles the type conversion and nulls internally.

user1529235 · Accepted Answer · 2017-08-01 12:36:27Z

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

Collectives™ on Stack Overflow

How to make R-code from SQL Server database?

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related