How to use PIVOT for varchar field

Hi Mijecarr,



Add the alias VesselName in the top select list, see below.

select CompanyCode,FirstName, LastName, eMail, VesselName,
       [1] as ContactType1, [2] as ContactType2, [3] as ContactType3, [4] as ContactType4, [5] as ContactType5,
       [6] as ContactType6, [7] as ContactType7, [8] as ContactType8, [9] as ContactType9, [10] as ContactType10,
  [11] as ContactType11, [12] as ContactType12, [13] as ContactType13, [14] as ContactType14, [15] as ContactType15     
from
(select c.CompanyCode, a.Name as VesselName, c.eMail, c.FirstName, c.LastName, 
        Cast(C.UserText10 as varchar(max)) as UserText10, 
        row_number()Over(Partition by c.eMail Order by eMail) rn 
from Contact  c left outer join Address a on cast (a.AddressID as varchar (50)) = C.UserText9 
where c.active = 1 and c.UserText10 <> 'DID') src 
Pivot(max(UserText10) for rn in([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15]))pvt 

If you have any question, feel free to let me know.

---

Eric Zhang

TechNet Community Support