Transpose rows into columns in MS SQL

Sometimes you need to transpose row values which have similar structure of data into columns and create Pivot like tables out of them.

Good example is to export data coming from Sitecore WFFM or Sitecore Forms tables.

Problem definition

Data are represented like this in table in rows:

FieldName    Value
--------------------------------
FirstName      Joe
MiddleName     S
LastName       Smith
Email          abc@abc.com
FirstName      Sam
MiddleName     S
LastName       Freddrick
Email          abc1@abc.com
FirstName      Jaime
MiddleName     S
LastName       Carol
Email          abc2@abc.com

 

You want to show that as Pivot tables and need to transpose rows into columns like this:

FirstName   MiddleName          LastName        Email
----------------------------------------------------------------------
Joe             S               Smith           abc@abc.com
Sam             S               Freddrick       abc1@abc.com
Jaime           S               Carol           abc2@abc.com

 

Solution

Solution is pretty easy.

I have created this simple temp table:

create table #Temp
(
    FormId int,
    FieldName Varchar(50), 
    Value Varchar(50)
)

Insert Into #Temp VALUES (1,'FirstName','Joe')
Insert Into #Temp VALUES (1,'MiddleName','S')
Insert Into #Temp VALUES (1,'LastName','Smith')
Insert Into #Temp VALUES (1,'Email','abc@abc.com')
Insert Into #Temp VALUES (2,'FirstName','Sam')
Insert Into #Temp VALUES (2,'MiddleName','S')
Insert Into #Temp VALUES (2,'LastName','Freddrick')
Insert Into #Temp VALUES (2,'Email','abc1@abc.com')
Insert Into #Temp VALUES (3,'FirstName','Jaime')
Insert Into #Temp VALUES (3,'MiddleName','S')
Insert Into #Temp VALUES (3,'LastName','Carol')
Insert Into #Temp VALUES (3,'Email','abc2@abc.com')

 

This is how the data is represented now:

 

By applying this simple select statement you can transpose row values into column values:

select FormId, max(FirstName) as FirstName, max(MiddleName) as MiddleName, max(LastName) as LastName, max(Email) as Email 
from (
    select FormId
         , case when FieldName = 'FirstName' then Value end as FirstName
         , case when FieldName = 'MiddleName' then Value end as MiddleName 
         , case when FieldName = 'LastName' then Value end as LastName 
         , case when FieldName = 'Email' then Value end as Email 
    from #Temp
) as t 
group by FormId;

 

This is the outcome of above select statement:

Just adjust the script to your values and that’s it!

Happy coding!