CODEWITHSUNDEEP
sqlsql-server
Rilcon42
Rilcon42

Reputation: 9763

How do you turn string values into columns in unpivot statement?

Im trying write an unpivot query that turns User data stored in columns into one row per user. Heres what I have so far, thanks to other SO posts: http://sqlfiddle.com/#!18/fa77c/2

Error (makes sense because "email" is a string stored in the Name column):

Invalid column name 'email'.

End goal:

ID Email   Phone
1  [email protected] 111
2  [email protected] 222
3  NULL    333

Upvotes: 0

Views: 1230

Answers (4)

Sean Lange
Sean Lange

Reputation: 33581

Not sure that unpivot is really what you want here. You can do this pretty easily with some aggregation.

select ac.AccountID
    , max(case when c.Name = 'email' then c.Data end) as Email
    , max(case when c.Name = 'phone' then c.Data end) as Phone
from AccountContacts ac
left join Contacts c on c.ID = ac.ContactID 
group by ac.AccountID

Please realize the reason your struggling here is because your data structure is an anti-pattern known as EAV (entity attribute value). It is a real pain to work with. If at all possible fixing your data structure would be a great plan.

Upvotes: 2

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

You can do conditional aggregation: 

SELECT ac.AccountID as ID,
       max(case when c.name = 'email' then c.data end) email,
       max(case when c.name = 'phone' then c.data end) phone
from Contacts c
left join AccountContacts ac on ac.ContactID = c.id
group by ac.AccountID;

Upvotes: 1

Sentinel
Sentinel

Reputation: 6449

Based on the desired results and the setup provided in your SQL Fiddle, you are looking to PIVOT your data, not UNPIVOT it.

This query does the trick:

with src as (
  select accountid
       , name
       , data
    from accountcontacts ac
    join contacts c
      on c.id = ac.contactid
)
select *
  from src
  pivot (max(data)
    for name in ([email],[phone])) pvt

Upvotes: 1

Daniel Marcus
Daniel Marcus

Reputation: 2686

  select a.data email, b.data phone from  
   (select * from Contacts a 
    join AccountContacts b on a.id=b.ContactID where name='email')a
 full join (
     select * from Contacts a 
     join AccountContacts b on a.id=b.ContactID where name='phone'
  )b on a.AccountID=b.AccountID

Upvotes: 0

Related Questions