BigQuery – Rotates rows into columns

Transforming BigQuery Rows to Columns

The Pivot operation in Google BigQuery changes rows into columns by using Aggregation. Let’s understand the working of the Pivot operator with the help of a table containing information about Products and their Sales per Quarter. The following examples reference a table called Produce that looks like this before applying the Pivot operation:

Example1

WITH Produce AS (
  SELECT 'Win' as product, 51 as sales, 'Q1' as quarter UNION ALL
  SELECT 'Win', 23, 'Q2' UNION ALL
  SELECT 'Win', 45, 'Q3' UNION ALL
  SELECT 'Win', 3, 'Q4' UNION ALL
  SELECT 'Linux', 77, 'Q1' UNION ALL
  SELECT 'Linux', 0, 'Q2' UNION ALL
  SELECT 'Linux', 25, 'Q3' UNION ALL
  SELECT 'Linux', 2, 'Q4')
SELECT * FROM Produce

+---------+-------+---------+
| product | sales | quarter |
+---------+-------+---------+
| Win     | 51    | Q1      |
| Win     | 23    | Q2      |
| Win     | 45    | Q3      |
| Win     | 3     | Q4      |
| Linux   | 77    | Q1      |
| Linux   | 0     | Q2      |
| Linux   | 25    | Q3      |
| Linux   | 2     | Q4      |
+---------+-------+---------+

After applying the Pivot operator, you can rotate the Sales and Quarter into Q1, Q2, Q3, and Q4 columns. This will make the table much more readable. The query for the same would look something like this:

SELECT * FROM
  (SELECT * FROM Produce)
  PIVOT(SUM(sales) FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4'))

+---------+----+----+----+----+
| product | Q1 | Q2 | Q3 | Q4 |
+---------+----+----+----+----+
| Win     | 51 | 23 | 45 | 3  |
| Linux   | 77 | 0  | 25 | 2  |
+---------+----+----+----+----+

Example2

WITH `table_name` AS (
  SELECT '1662104425' `ga_session_id`, 'page_a' AS `page`, 'r1' AS `rank` UNION ALL
  SELECT '1662104425', 'page_b', 'r2' UNION ALL
  SELECT '1662104425', 'page_c', 'r3' UNION ALL
  SELECT '1662104425', 'page_d', 'r4' UNION ALL
  SELECT '1662104425', 'page_e', 'r5' UNION ALL
  SELECT '1662091784', 'page_b', 'r1' UNION ALL
  SELECT '1662091784', 'page_c', 'r2' UNION ALL
  SELECT '1662091784', 'page_d', 'r3'
)  
SELECT * FROM table_name

Row	ga_session_id	page	rank
1	1662104425	page_a	r1
2	1662104425	page_b	r2
3	1662104425	page_c	r3
4	1662104425	page_d	r4
5	1662104425	page_e	r5
6	1662091784	page_b	r1
7	1662091784	page_c	r2
8	1662091784	page_d	r3

ใช้ ANY_VALUE

Functions, operators, and conditionals | BigQuery | Google Cloud

SELECT * FROM table_name
PIVOT(any_value(page) FOR rank IN ('r1', 'r2', 'r3', 'r4', 'r5'))

Row	ga_session_id	r1	r2	r3	r4	r5
1	1662104425	page_a	page_b	page_c	page_d	page_e
2	1662091784	page_b	page_c	page_d	null	null