Create table #payments  (
row_id int,
_id varchar(20),
point_id int, 
client_id int
)
insert into #payments  (row_id, _id, point_id, client_id)
values (57095, '111-222-333', 4, 5)

Create table #payments_attrib (
row_id int,
payment_id int,
_name varchar(20), 
_value varchar(20),
)
insert into #payments_attrib  (row_id, payment_id, _name, _value)
values (90120, 57095, 'account', '7077017'),
(90122, 57095, 'brand', 'ACT'),
(90121, 57095, 'location', 'MST'),
(90123, 57095, 'payMethod', 'PAA'),
(90124, 57095, 'rtt', '021456668')


DECLARE @guid as nvarchar(250)
SET @guid= '111-222-333'

Select #t.* From #payments
JOIN (
SELECT row_id = (SELECT TOP(1) row_id FROM #payments_attrib WHERE payment_id = '57095' ORDER BY row_id)
     , payment_id
    , MAX(CASE WHEN _name = 'account' THEN _value ELSE '' END) [account]
    , MAX(CASE WHEN _name = 'brand' THEN _value ELSE '' END) [brand]
    , MAX(CASE WHEN _name = 'location' THEN _value ELSE '' END) [location]
    , MAX(CASE WHEN _name = 'payMethod' THEN _value ELSE '' END) [payMethod]
    , MAX(CASE WHEN _name = 'rtt' THEN _value ELSE '' END) [rtt]
FROM #payments_attrib
WHERE payment_id = '57095'
GROUP BY payment_id
) as #t ON(#payments.row_id = #t.payment_id)
WHERE 
_id=@guid 

drop table #payments_attrib
drop table #payments