Creating new category columns with corresponding values from a SELECT statement

I’m trying to create a SELECT statement where the values under “category” and “category2” column will become actual columns and show the corresponding “url” and “url2” under them.

Basically I have the following table:

      names       |    category    |            url            |   category2  |         url2             
------------------+----------------+---------------------------+--------------+------------------------------
  Jennifer        | Homepage       | http://www.example1.com   |              | 
  Jacob           | Github         | http://www.github.com     |              |  
  Tom             | Homepage       | http://www.example2.com   |    Github    | http://www.github2.com
  Nal             | Facebook Page  | http://www.facebook.com   |              | 
  Matt            | Homepage       | http://www.example3.com   |              |
  Daniel          |                |                           |    Homepage  | http://www.example4.com
  Sarah           |                |                           |    Other     | http://www.other_example.com

…and I want the end result to look like this:

         names     |        Homepage          |         Github         |     Facebook Page       |        Other           
-------------------+--------------------------+------------------------+-------------------------+---------------------
    Jennifer       | http://www.example1.com  |                        |                         |
    Jacob          |                          | http://www.github.com  |                         |
    Tom            | http://www.example2.com  | http://www.github2.com |                         |
    Nal            |                          |                        | http://www.facebook.com |
    Matt           | http://www.example3.com  |                        |                         |
    Daniel         | http://www.example4.com  |                        |                         | 
    Sarah          |                          |                        |                         | http://www.other_example.com

Any ideas? I don’t know which options to even look for? I’m using PostgreSQL.

Demo v3

Answer

For these 3 categories you can use conditional aggregation:

select
  names,
  max(case category when 'Homepage' then url end) Homepage,
  max(case category when 'Github' then url end) GitHub,
  max(case category when 'Facebook Page' then url end) "Facebook Page"
from tablename
group by names

See the demo.
Results:

| names    | homepage                 | github                | Facebook Page            |
| -------- | ------------------------ | --------------------- | ------------------------ |
| Jennifer | http://www.example1.com  |                       |                          |
| Tom      | http://www.example2.com  |                       |                          |
| Jacob    |                          | http://www.github.com |                          |
| Nal      |                          |                       | https://www.facebook.com |
| Matt     | https://www.example3.com |                       |                          |

Edit

select
  t.names,
  max(case t.category when 'Homepage' then t.url end) Homepage,
  max(case t.category when 'Github' then t.url end) GitHub,
  max(case t.category when 'Facebook Page' then t.url end) "Facebook Page",
  max(case t.category when 'Other' then t.url end) Other
from (
  select names, category, url from tablename
  union all
  select names, category2, url2 from tablename
) t     
group by t.names

See the demo.
Results:

| names    | homepage                | github                 | Facebook Page           | other                        |
| -------- | ----------------------- | ---------------------- | ----------------------- | ---------------------------- |
| Jennifer | http://www.example1.com |                        |                         |                              |
| Sarah    |                         |                        |                         | http://www.other_example.com |
| Tom      | http://www.example2.com | http://www.github2.com |                         |                              |
| Jacob    |                         | http://www.github.com  |                         |                              |
| Nal      |                         |                        | http://www.facebook.com |                              |
| Daniel   | http://www.example4.com |                        |                         |                              |
| Matt     | http://www.example3.com |                        |                         |                              |