0

I have a situation where I am stuck to create a query for fetching data from Wffm DB as Pivot, so please help

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

Expected

FirstName   MiddleName          LastName        Email
----------------------------------------------------------------------
Joe             S               Smith           abc@abc.com
Sam             S               Freddrick       abc1@abc.com
Jaime           S               Carol           abc2@abc.com
  • What is your query? – Richard Seal Jan 22 at 22:10
  • 1
    I'm voting to close this question as off-topic because this question is about SQL not Sitecore. – Harsh Baid Feb 18 at 15:00
2

I remember that there is also FormId field in the same table as you are referencing.

I have created a similar temporar table to the one from Sitecore WFFM 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')

Table looks like this:

enter image description here

And this is the script to transpose rows into columns:

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;

Table looks like this at the end:

enter image description here

Just change the column and table names and you should be fine...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.