We have been using different methods in SQL to convert row data into columns. Some time it’s just the requirement for data visualisation only. To fulfil this requirement mostly we use PIVOT, which is complex and lengthy. In this blog post we will see how can we leverage R in SQL to get our required output?
In case you are new to R, it is a very powerful and popular statistical programming language widely used by data scientist for data analytics. Microsoft has integrated R with SQL Server 2016, it means we can execute R scripts via a system-stored procedure using T-SQL. Query data, pass it to R, and get the results back into SQL Server. Isn’t that amazing?
Note: You need to have R on the server where your SQL instance is installed. Getting started with R
/* --one time setup EXEC sp_configure N'external scripts enabled', 1 GO RECONFIGURE */ --Actual data SELECT BusinessEntityID, FirstName + SPACE(1) + LastName As Name, JobTitle FROM HumanResources.vEmployee WHERE BusinessEntityID <= 5 --Required output execute sp_execute_external_script @language = N'R' , @script = N' SQLOut <- as.data.frame(t(SQLIn));' , @input_data_1 = N' SELECT BusinessEntityID, FirstName + SPACE(1) + LastName, JobTitle FROM HumanResources.vEmployee WHERE BusinessEntityID <= 5;' , @input_data_1_name = N'SQLIn' , @output_data_1_name = N'SQLOut' /* Uncomment this if you need column names WITH RESULT SETS (([Employee1] VARCHAR(50) NOT NULL , [Employee2] VARCHAR(50) NOT NULL , [Employee3] VARCHAR(50) NOT NULL , [Employee4] VARCHAR(50) NOT NULL , [Employee5] VARCHAR(50) NOT NULL)) */