0

I have a data table in BigQuery which looks similar to this.

enter image description here

My desired outcome is this:

enter image description here

I've been experimenting with the MAX (CASE WHEN Country = 'France' THEN 'yes' END) as France to create columns where I can identify whether the customer made a purchase in the country, but not the number of all their purchases. Any help gratefully received.

user3156990
  • 97
  • 1
  • 10

1 Answers1

2

Try countif:

select
  customer,
  countif(Country='France') as France,
  countif(Country='UK') as UK,
  countif(Country='Ireland') as Ireland
from mytable
group by customer

or pivot operator:

SELECT *
FROM mytable
PIVOT(COUNT(TransactionId) FOR Country IN ('France', 'UK', 'Ireland'))
Sergey Geron
  • 9,098
  • 2
  • 22
  • 29
  • The countif solution worked perfectly. I was unable to test the pivot operator at this time as it returned an error, saying that the field name was invalid. The real data contains spaces, so I'd need to clean this up first. Many thanks. – user3156990 Jun 02 '21 at 07:47