Ben Leathers Ben Leathers - 1 year ago 74
SQL Question

Reconfiguring a Table in BigQuery

Not sure what functions to call, but transpose is the closest thing I can think of.

I have a table in BigQuery that is configured like this:
enter image description here

but I want to query a table that is configured like this:

enter image description here

What does the SQL code look like for creating this table?

Thanks!

Answer Source

Use the UNION of tables (with ',' in BigQuery), plus some column aliasing:

SELECT Location, Size, Quantity
FROM (
  SELECT Location, 'Small' as Size, Small as Quantity FROM [table]
), (
  SELECT Location, 'Medium' as Size, Medium as Quantity FROM [table]
), (
  SELECT Location, 'Large' as Size, Large as Quantity FROM [table]
)