How to Transpose Rows into Columns in BigQuery?

Transpose Rows into Columns in BigQuery: A Pivoting Implementation

Transposing rows into columns, also known as pivoting, allows you to convert key-value pairs into a new table where the keys become column names and the values are placed in the corresponding cells. While BigQuery does not natively support pivoting functions, you can still achieve this using a straightforward approach.

Input Table Structure

To begin, ensure your input table has the following columns:

• Key: The column that designates the category for each row.
• Value: The value that corresponds to the key.

Additionally, you will need a column that groups rows that should be combined into a single row in the output.

Step 1: Converting Input Data into a Transposed Query String

In Step 1, we will create a query string that, when executed, will generate the transposed query. Execute the following query:

SELECT 'SELECT id, ' + 
   GROUP_CONCAT_UNQUOTED(
      'MAX(IF(key = "' + key + '", value, NULL)) as [' + key + ']'
   ) 
   + ' FROM yourTable GROUP BY id ORDER BY id'
FROM (
  SELECT key 
  FROM yourTable
  GROUP BY key
  ORDER BY key
) 

Step 2: Executing the Transposed Query

Copy the result from Step 1 and execute it as a regular query. This will produce a table with the transposed data:

SELECT 
  id, 
  MAX(IF(key = "channel_id", value, NULL)) AS [channel_id],
  MAX(IF(key = "channel_title", value, NULL)) AS [channel_title],
  MAX(IF(key = "examId", value, NULL)) AS [examId],
  MAX(IF(key = "postId", value, NULL)) AS [postId],
  MAX(IF(key = "youtube_id", value, NULL)) AS [youtube_id] 
FROM yourTable 
GROUP BY id 
ORDER BY id

The resulting table will have the following structure:

id	channel_id	channel_title	examId	postId	youtube_id
1	UCiDKcjKocimAO1tV	Mahendra Guru	72975611-4a5e-11e5	1189e340-b08f	ugEGMG4-MdA
2	UCODeKM_D6JLf8jJt	Ab Live	72975611-4a5e-11e5	0c3e6590-afeb	3TNbtTwLY0U

Note

If your table has a large number of columns, you can skip Step 1 by manually constructing the transposed query. However, Step 1 provides a quick and convenient way to generate the query dynamically.

The above is the detailed content of How to Transpose Rows into Columns in BigQuery?. For more information, please follow other related articles on the PHP Chinese website!