Pivot in BIG QUERY giving error for row data having a space . Fields must contain only letters numbers underscores start with a letter or underscore

user_15113029 0
Viewed 498

I am trying to do PIVOTING in Big query by using below query

select * from
(SELECT desc,qty,RSN_DESC FROM `TABLE`)
  PIVOT(SUM(qty) FOR RSN_DESC IN ('Example1', 'Example2', 'Example3', 'Example 3','Example4','Example 5',''))

If you look closely the "Example 3" and "Example 5" we have a space in between data, like "Store Ops" (an actual example from my data). Pivot does not work for such data and it gives error as

Invalid field name "Store Ops". Fields must contain only letters, numbers, and underscores, start with a letter or underscore, and be at most 300 characters long.

Any suggestions on how can I handle this scenario?

google-bigquery google-cloud-platform
1 Answers

Use below instead

select * from
(SELECT `desc`,qty,RSN_DESC FROM `TABLE`)
PIVOT(SUM(qty) FOR replace(RSN_DESC, ' ', '_') IN ('Example1', 'Example2', 'Example3', 'Example_3','Example4','Example_5',''))

in this case 'Example3', 'Example_3' will be different columns

or

select * from
(SELECT `desc`,qty,RSN_DESC FROM `TABLE`)
PIVOT(SUM(qty) FOR replace(RSN_DESC, ' ', '') IN ('Example1', 'Example2', 'Example3', 'Example4','Example5',''))

in this case 'Example3' and 'Example 3' will be collapsed into one column

user_52219440
Related