Pivot / Transform rows from one column into multiple columns with R in SqlServer

Let’s say we have a table  [CurrencyValues] with the following datas

We want to transform the CurrencyID into columns( i.e. a column with CAD,other with EUR,other with GBP and so on) .

We can utilize R from SqlServer (https://msdn.microsoft.com/en-us/library/mt604885.aspx )

We have 2 prerequisites;

1. R is running inside SqlServer

2. The library tidyR is downloaded

( see the final of this post to verify)

Then we execute the following script:

execute sp_execute_exterhttp://msprogrammer.serviciipeweb.ro/wp-admin/widgets.phpnal_script
@language = N'R'
,@script = N'
library(tidyr);
model<-spread(InputDataSet,CurrencyID,CurrencyValue);
OutputDataSet <- model'
,@input_data_1 = N'select   CurrencyID,CurrencyValue,ValueDate  from CurrencyValues '

WITH RESULT SETS undefined

The output is this:

The problem is that we have columns without names (spread returns data frame columns names,but SqlServer does not … yet,I hope)

So we add the column names:

execute sp_execute_external_script
@language = N'R'
,@script = N'
library(tidyr);
model<-spread(InputDataSet,CurrencyID,CurrencyValue);
cols<-paste(colnames(model),collapse = ",");
model<-cbind(ColumnNames = cols,model);

OutputDataSet <- model'
,@input_data_1 = N'select   CurrencyID,CurrencyValue,ValueDate  from CurrencyValues '

WITH RESULT SETS undefined

and  the result is slightly better:

What I want? Sql Server should return column names in data frame by default …

And that is all  –  execute sp_execute_external_script have the possibility of parameters,so you can make general.

End!

——————————————————————-

^{Note 1}: R is running inside SqlServer

Verify

exec sp_execute_external_script  
  @language =N'R',
  @script=N'OutputDataSet<-InputDataSet',
  @input_data_1 =N'select 1 as hello'    
  with result sets (([hello] int not null));    
go

If it gives error,then follow https://msdn.microsoft.com/en-us/library/mt696069.aspx  – basic is

Exec sp_configure  'external scripts enabled',1  
Reconfigure  with  override

and restart SqlServer

^{Note 2}: Installing package tidyr

Follow https://msdn.microsoft.com/en-us/library/mt591989.aspx : basic is starting R with admin rights and executing

lib.SQL <- "C:\\Program Files\\Microsoft SQL Server\\MSSQL13.MSSQLSERVER\\R_SERVICES\\library"

install.packages("tidyr",lib = lib.SQL)

and that will be all ( ok,you may find some additional libraries to install)