Ben Leathers Ben Leathers - 4 months ago 22
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

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]
)