How to transform a result set so column values become aggregated headers?

If I have a table:

Id Collection Site
1 Example Collection stackoverflow.com
2 Example Collection news.ycombinator.com
1 Example Collection stackoverflow.com
3 Example Collection reddit.com
4 Programming Sites stackoverflow.com
5 Discussion Sites reddit.com

How can I transform it to...

Collection stackoverflow.com news.ycombinator.com reddit.com
Example Collection 2 1 1
Programming Sites 1 0 0
Discussion Sites 0 0 1

In the real system which works similarly, there'll be 100k+ rows in the original table, and thousands of distinct "sites." It's desirable for the transformation to stay in SQL.

1 answer

  • answered 2021-07-27 13:16 Gordon Linoff

    Assuming you have only those three sites, you can use conditional aggregation:

    select collection,
           sum(case when site = 'stackoverflow.com' then 1 else 0 end) as stackoverflow_com,
           sum(case when site = 'news.ycombinator.com' then 1 else 0 end) as news_ycombinator_com,
           sum(case when site = 'reddit.com' then 1 else 0 end) as reddit_com
    from t
    group by collection;
    

How many English words
do you know?
Test your English vocabulary size, and measure
how many words do you know
Online Test
Powered by Examplum