Pivot outputs two values

Question

create table #Contact(
 LoanNumber int,
 ContactType varchar(10),
 CompanyName varchar(10),
 CompanyPhone varchar(10),
 CONSTRAINT PK PRIMARY KEY (LoanNumber,ContactType)
)

Insert into #Contact
values (1,'Appriaser','Yige King','11' ),
       (1,'AssetOwner','gqqnbig','22' )

This is my table. ContactTypes are only Appriaser and AssetOwner.

Can I get a table like

LoanNumber  AppraiserCompanyName    AppraiserCompanyPhone   AssertOwnerCompanyName  AssertOwnerCompanyPhone
----------------------------------------------------------------------------------------------------
6103339     YigeKing                11                      gqqnbig                 22

I managed to write this

select LoanNumber,
        CompanyNamePT.Appriaser as AppriaserCompanyName, CompanyNamePT.AssetOwner as AssetOwnerCompanyName
        --CompanyPhonePT.Appriaser as AppriaserCompanyPhone, CompanyPhonePT.AssetOwner as AssetOwnerCompanyPhone
from  (
    select #contact.LoanNumber, #contact.ContactType, #contact.CompanyName
    from #contact
) as c
pivot ( max(c.CompanyName) for c.ContactType in (Appriaser,AssetOwner)) as CompanyNamePT
--pivot ( max(c.CompanyPhone) for c.ContactType in ([Appriaser],[AssetOwner])) as CompanyPhonePT

It outputs company names, but if I uncomment the two lines to get phone number, it throws syntax error.

How can I make it work? Ideally I want to use pivot because I'm learnig it.

Answer 1

For the desired PIVOT

Select *
 From (
        Select C.LoanNumber
              ,B.*
         From  #Contact C
         Cross Apply ( values (IIF(ContactType='Appriaser'  ,'AppraiserCompanyName' , 'AssetOwnerCompanyName') ,C.CompanyName)
                             ,(IIF(ContactType='Appriaser' , 'AppraiserCompanyPhone', 'AssetOwnerCompanyPhone'),C.CompanyPhone)
                     ) B (Item,Value)
      ) A
pivot ( max(A.Value) for A.Item in ([AppraiserCompanyName],[AppraiserCompanyPhone],[AssetOwnerCompanyName],[AssetOwnerCompanyPhone]) ) P

But a Conditional Aggregation would do as well

Select C.LoanNumber
      ,AppraiserCompanyName   =  max(case when ContactType='Appriaser'  then C.CompanyName end)
      ,AppraiserCompanyPhone  =  max(case when ContactType='Appriaser'  then C.CompanyPhone end)
      ,AssetOwnerCompanyName  =  max(case when ContactType='AssetOwner' then C.CompanyName end)
      ,AssetOwnerCompanyPhone =  max(case when ContactType='AssetOwner' then C.CompanyPhone end)
From #Contact C
Group By C.LoanNumber

Both Would Return

If it Helps with the Visualization, the sub-query with the Cross Apply Generates