I'm trying to pivot a table using SQL. My table looks like this:

And the expected outcome is this one:

I'm using the following code, but for some reason I can't make it work:

select id_account,[Authorized],[Rejected]

from 
(select id_account, import, status_name as status,
  from `mytable` t7
  where transaction_type_code in (6,9) and authorization_date > '2022-06-01' and authorization_date <= '2022-06-30') 
  as src

pivot
  (sum(import) for status in ([Authorized], [Rejected])) as pvt

The error I get is that it doesn't recognize [Authorized], but it's one of the options that I have on the status_name column. More specifically, the error says: Unrecognized name: Authorized at [17:26]

Does anybody know why I might be getting this error?

CodePudding user response：

You need to provide pivot columns as string literals. Consider below query.

SELECT * FROM (
  SELECT ID, import, status FROM mytable
) PIVOT (SUM(import) FOR status in ('Authorized', 'Rejected'));

• https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#pivot_operator