Transpose rows into columns in BigQuery (Pivot implementation) [duplicate]

Question

I want to generate a new table and place all key value pairs with keys as column names and values as their respective values using BigQuery.

Example:

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

channel_title           Ab Live  
youtube_id              3TNbtTwLY0U  
channel_id              UCODeKM_D6JLf8jJt    
examId                  72975611-4a5e-11e5   
postId                  0c3e6590-afeb

I want to convert it to:

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

How to do it using BigQuery?

Answer 1

BigQuery does not support yet pivoting functions
You still can do this in BigQuery using below approach

But first, in addition to two columns in input data you must have one more column that would specify groups of rows in input that needs to be combined into one row in output

So, I assume your input table (yourTable) looks like below

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

   2    channel_title           Ab Live  
   2    youtube_id              3TNbtTwLY0U  
   2    channel_id              UCODeKM_D6JLf8jJt    
   2    examId                  72975611-4a5e-11e5   
   2    postId                  0c3e6590-afeb  

So, first you should run below 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
) 

Result of above query will be string that (if to format) will look like below

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

you should now copy above result (note: you don't really need to format it - i did it for presenting only) and run it as normal query

Result will be as you would expected

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  

Please note: you can skip Step 1 if you can construct proper query (as in step 2) by yourself and number of fields small and constant or if it is one time deal. But Step 1 just helper step that makes it for you, so you can create it fast any time!

If you are interested - you can see more about pivoting in my other posts.

How to scale Pivoting in BigQuery?
Please note – there is a limitation of 10K columns per table - so you are limited with 10K organizations.
You can also see below as simplified examples (if above one is too complex/verbose):
How to transpose rows to columns with large amount of the data in BigQuery/SQL?
How to create dummy variable columns for thousands of categories in Google BigQuery?
Pivot Repeated fields in BigQuery