How to Unpivot Data in BigQuery to Restructure Quarterly Sales Data?

Unpivoting in BigQuery

Question:

How do I restructure a table from having rows of data for different quarters to having columns for the quarters? For example, converting a table like this:

| product | Q1 | Q2 | Q3 | Q4 |
|---|---|---|---|---|
| Kale | 51 | 23 | 45 | 3 |
| Apple | 77 | 0 | 25 | 2 |

into a table with columns for sales and quarter:

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

Answer:

Update 2021:

BigQuery now includes an UNPIVOT operator that can rotate rows of data into columns.

Before UNPIVOT:

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

The above is the detailed content of How to Unpivot Data in BigQuery to Restructure Quarterly Sales Data?. For more information, please follow other related articles on the PHP Chinese website!