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.
Advertisement
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 | | | |