CODEWITHSUNDEEP
google-bigquery
Dave Papworth
Dave Papworth

Reputation: 313

How can I pivot this query in BigQuery

I've got a query in Bigquery which i'd like to pivot the output on Project Site Name and Study ID.

This is the code:

SELECT
    Project_Site_Name,
    NIHR_Portfolio_Study_ID,
    CASE WHEN (Recruitment_Year = '2017/18')
         THEN COUNT(Patient_Local_Number) END AS rec1718,
    CASE WHEN (Recruitment_Year = '2018/19')
         THEN COUNT(Patient_Local_Number) END AS rec1819
FROM `fourth-jigsaw-118116.Partners.PARData`
WHERE
    NIHR_Portfolio_Study_ID = 1358 AND
    Recruitment_Year IN ('2017/18', '2018/19')
GROUP BY
    Project_Site_Name,
    NIHR_Portfolio_Study_ID,
    Recruitment_Year

Currently, this is the output:

enter image description here

And I'd like it to look like this:

enter image description here

I've been searching for a solution for some time now, I think it might have something to do with nesting the aggregation in a subquery but that's about as far as i've got. Any suggestions?

best wishes

Dave

Upvotes: 0

Views: 48

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521289

If you want to pivot out result columns based on the recruitment year, then you should not be grouping by that column. Instead, use conditional aggregation to determine the counts for each recruitment year.

SELECT
    Project_Site_Name,
    NIHR_Portfolio_Study_ID,
    COUNT(CASE WHEN Recruitment_Year = '2017/18' THEN 1 END) AS rec1718,
    COUNT(CASE WHEN Recruitment_Year = '2018/19' THEN 1 END) AS rec1819
FROM fourth-jigsaw-118116.Partners.PARData
WHERE
    NIHR_Portfolio_Study_ID = 1358 AND
    Recruitment_Year IN ('2017/18', '2018/19')
GROUP BY
    Project_Site_Name,
    NIHR_Portfolio_Study_ID;

Upvotes: 4

Related Questions