18 Dec 18 · victor · #bigquery ·   Bookmark   ×

How to convert Array of columns to rows in Bigquery

I have a column as below screenshot in my google bigquery

i need to convert that column to rows as below in Bigquery :

70042 70055 70044 70046 70042 70055 70044 70046

#standardSQL
SELECT id, dspid
FROM `veuhub-185502.AdtechAnalytics.tag_request`,
UNNEST(dspid) dspid 

or below for (based on your comments)

#standardSQL
SELECT 
  DSPID, 
  adtype, 
  adtypeWithDevice, 
  EXTRACT(year FROM request_timestamp) AS year, 
  EXTRACT(month FROM request_timestamp) AS month, 
  EXTRACT(day FROM request_timestamp) AS day, 
  EXTRACT(hour FROM request_timestamp) AS hour, 
  SUM(1) AS requestcount 
FROM `veuhub-185502.AdtechAnalytics.tag_request`, 
UNNEST(dspid) dspid 
GROUP BY adtype,adTypeWithDevice,DSPID,year,month,day,hour

victor

posted on 18 Dec 18

Enjoy great content like this and a lot more !

Signup for a free account to write a post / comment / upvote posts. Its simple and takes less than 5 seconds

Copied