select/union output from two columns into three
After your clarifying comment I suggest:

SELECT hostname, r.value AS role, e.value AS env 
FROM   hostname_facts r
JOIN   hostname_facts e USING (hostname)
WHERE  r.name = 'role'
AND    e.name = 'env'
ORDER  BY hostname;


It does the same as the query @zgguy already posted, just simpler.