SQL Pivot in BigQuery

I have a SQL table with information about email campaigns that my company has created. Each line of the table is an action that a user has taken on a specific campaign:

User ID	Campaign Name	Status
01	Campaign#1	opened
01	Campaign#1	clicked
01	Campaign#2	opened
02	Campaign#1	opened
02	Campaign#2	opened

I wanted to Pivot this on SQL, in a way that would render the unique number of people who have opened and clicked on each campaign:

Campaign Name	Opened	Clicked
Campaign#1	2149	122
Campaign#2	4223	141

I’ve been trying to work with: SELECT user_id, campaign_name, status from table PIVOT( COUNT (DISTINCT user_id) FOR status IN ( [opened], [clicked] ) ) AS PivotTable

But then I am getting: Unrecognized name: opened at [5:6]

Answer

Consider below example

select * from your_table
pivot (count(distinct UserID) for Status in ('opened', 'clicked'))     

if applied to sample data in your question

with your_table as (
  select '01' UserID, 'Campaign#1' CampaignName, 'opened' Status union all
  select '01', 'Campaign#1', 'clicked' union all
  select '01', 'Campaign#2', 'opened' union all
  select '02', 'Campaign#1', 'opened' union all
  select '02', 'Campaign#2', 'opened' 
)            

the output is