Menu
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:
but I want to query a table that is configured like this:
What does the SQL code look like for creating this table?
Thanks!
391
The syntax for the UNPIVOT operator is similar to the PIVOT one. In the SELECT statement, you need to specify the columns you want to add to the output table. In the UNPIVOT statement, you will specify two columns: The first column contains the values from the rows of the pivoted columns (which is Score in this case).
When you unpivot, you unpack the attribute-value pairs that represent an intersection point of the new columns and re-orient them into flattened columns: Values (in blue on the left) are unpivoted into a new column (in blue on the right).
OFFSET means that the numbering starts at zero, ORDINAL means that the numbering starts at one. A given array can be interpreted as either 0-based or 1-based. When accessing an array element, you must preface the array position with OFFSET or ORDINAL , respectively; there is no default behavior.
Click the join and choose Delete. Create a new join by dragging the column name from the second table to the corresponding column name in the first table. Click Execute SQL. If a preview of your data appears in the Sample Preview pane, the join was successfully created.
Update 2021:
A new UNPIVOT operator has been introduced into BigQuery.
Before UNPIVOT is used to rotate Q1, Q2, Q3, Q4 into sales and quarter columns:
| product | Q1 | Q2 | Q3 | Q4 |
|---|---|---|---|---|
| Kale | 51 | 23 | 45 | 3 |
| Apple | 77 | 0 | 25 | 2 |
After UNPIVOT is used to rotate Q1, Q2, Q3, Q4 into sales and quarter columns:
| product | sales | quarter |
|---|---|---|
| Kale | 51 | Q1 |
| Kale | 23 | Q2 |
| Kale | 45 | Q3 |
| Kale | 3 | Q4 |
| Apple | 77 | Q1 |
| Apple | 0 | Q2 |
| Apple | 25 | Q3 |
| Apple | 2 | Q4 |
Query:
WITH Produce AS (
SELECT 'Kale' as product, 51 as Q1, 23 as Q2, 45 as Q3, 3 as Q4 UNION ALL
SELECT 'Apple', 77, 0, 25, 2
)
SELECT * FROM Produce
UNPIVOT(sales FOR quarter IN (Q1, Q2, Q3, Q4))
@Felipe, I tried this using standard-sql but I get an error on the first line of your query that says: "Column name Location is ambiguous at [1:8]"
I've used an alternate query that works for me:
SELECT Location, 'Small' as Size, Small as Quantity FROM `table`
UNION ALL
SELECT Location, 'Medium' as Size, Medium as Quantity FROM `table`
UNION ALL
SELECT Location, 'Large' as Size, Large as Quantity FROM `table`
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
