Or if you prefer the pivot syntax (which lets face it, nobody does)....

;with t (SID, _Name, Data) as

(

select 1, 'Name', 'Joe' union all

select 1, 'Email Address', 'Joe.Doe@somewhere.com' union all

select 1, 'Comment', 'Test' union all

select 2, 'Name', 'SomeoneElse' union all

select 2, 'Email Address', 'SomeoneElse@somewhere.com' union all

select 2, 'Comment', 'Another Test'

)

select *

from t

pivot (max(Data) for _Name in (Name, [Email Address], Comment)) p