How to Pivot Rows into Columns in BigQuery?

Pivot Transformation in BigQuery: Transposing Rows into Columns

Problem:

The objective is to transform data from rows to columns in BigQuery, where the keys become column names and the values become the corresponding column values.

Example:

Before:

Key	Value
channel_title	Mahendra Guru
youtube_id	ugEGMG4-MdA
examId	72975611-4a5e-11e5
channel_id	UCiDKcjKocimAO1tV
postId	1189e340-b08f

After:

channel_title	youtube_id	channel_id	examId	postId
Mahendra Guru	ugEGMG4-MdA	UCiDKcjKocimAO1tV	72975611-4a5e-11e5	1189e340-b08f

Solution:

BigQuery currently lacks native pivoting functions. However, the following approach can achieve the same result:

Step 1: Generate Query Structure

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: Run Query and Transform

• Run the generated query from Step 1.
• Copy the result and format it as follows (formatting is not required):

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

• Run the transformed query.

Considerations:

• You can skip Step 1 if you can manually construct the query in Step 2.
• There is a limitation of 10K columns per table, so consider scaling for larger datasets.

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