SQL: how to pivot dimensions in a column?

I’m looking to do the following:

Raw table: this has just two articles, their traffic channels, and unique pageviews

SELECT * FROM website_content_table

Returns:

I want to return a table that looks like this:

Here’s my current solution – which I think is inelegant and slow. There’s surely an easier way to do this in BigQuery SQL (note – I don’t have a pivot function I can use)

SELECT pageTitle, 
      sum(unique_pageviews) AS unique_pageviews, 
      sum(organic_unique_pageviews) AS organic_unique_pageviews,
      sum(email_unique_pageviews) AS email_unique_pageviews,
      sum(paid_unique_pageviews) AS paid_unique_pageviews     
FROM (
             -- table for total unique_pageviews
            (SELECT pageTitle, 
                   sum(unique_pageviews) unique_pageviews
            FROM website_content_table 
            GROUP BY pageTitle) AS a

            -- table for organic  unique_pageviews
            LEFT JOIN 
                    (SELECT pageTitle,
                            sum(unique_pageviews) organic_unique_pageviews
                     FROM website_content_table 
                     WHERE traffic_channel = 'Organic'
                     GROUP BY pageTitle) AS organic
            ON a.pageTitle = organic.pageTitle  

            -- table for email  unique_pageviews
            LEFT JOIN 
                    (SELECT pageTitle,
                            sum(unique_pageviews) email_unique_pageviews
                     FROM website_content_table 
                     WHERE traffic_channel = 'Email'
                     GROUP BY pageTitle) AS email
            ON a.pageTitle = email.pageTitle  

            -- table for paid  unique_pageviews
            LEFT JOIN 
                    (SELECT pageTitle,
                            sum(unique_pageviews) paid_unique_pageviews
                     FROM website_content_table 
                     WHERE traffic_channel = 'Paid'
                     GROUP BY pageTitle) AS paid
            ON a.pageTitle = paid.pageTitle  
)
GROUP BY pageTitle
ORDER BY unique_pageviews

Answer

There is a much simpler way to do this using a pivot query with conditional aggregation:

SELECT
    pageTitle,
    SUM(unique_pageviews) AS unique_pageviews,
    SUM(CASE WHEN traffic_channel = 'Organic' THEN unique_pageviews ELSE 0 END) AS Organic,
    SUM(CASE WHEN traffic_channel = 'Email'   THEN unique_pageviews ELSE 0 END) AS Email,
    SUM(CASE WHEN traffic_channel = 'Paid'    THEN unique_pageviews ELSE 0 END) AS Paid
FROM yourTable
GROUP BY
    pageTitle;

Advertisement

Answer