PostgreSQL – How to Select/Union Output from Two Columns into Three

postgresql

I'm sure there is an easy answer to this, but my PostgreSQL isn't up to it at the moment.

I have a table hostname_facts with three columns:

hostname
name
value

hostname is a list of fully-qualified server hostnames.
name contains things like role (a TLA defining a server's role in the environment), env (part of an FQDN), memorysize, eth1_ip, fqdn, etc.
value – the actual value of role, env, memorysize, eth1_ip, fqdn, etc.

There are about 800 hostnames in this table.

From this table I've been asked to create a flat file that can be used as an inventory list for Ansible, which needs to be in the format:

[role.env]
hostname, value_of_role, value_of_env
hostname, value_of_role, value_of_env
hostname, value_of_role, value_of_env
...

So, I need need to pull out hostname sorted by value of name = 'role' as well as name = 'env'. This is stumping me!

I used:

select hostname,value from hostname_facts
where name = 'role'
union
select hostname,value from hostname_facts
where name = 'env'
order by value ASC

But that returns the 'name' values all in one long column, 'role' followed by 'env', with all hostnames repeated twice (obviously).

Can anyone suggest how to get just one set of hostnames with 'role' and 'env' as two columns alongside, rather than one underneath the other?

Best Answer

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.

Related Question