CODEWITHSUNDEEP
google-bigquery
mez63
mez63

Reputation: 166

How do I pivot in big query

Say I have data

id,col1,col2,col3,col4,col5

1,a,b,c,d,e

and I want the result to be ...

1,a

1,b

1,c

1,d

1,e

How do I pivot on id in big query ?

Upvotes: 1

Views: 828

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172994

Below is for BigQuery Standard SQL 

#standardSQL
CREATE TEMP FUNCTION cols_to_rows(root STRING) AS (
  ARRAY(SELECT REPLACE(SPLIT(kv, ':') [OFFSET(1)], '"', '') cols 
  FROM UNNEST(SPLIT(REGEXP_REPLACE(root, r'^{|}$', ''))) kv
  WHERE SPLIT(kv, ':') [OFFSET(0)] != '"id"'
  )
);
SELECT id, col
FROM `project.dataset.table` t,
UNNEST(cols_to_rows(TO_JSON_STRING(t))) col

You can test / play with above using dummy data as below 

#standardSQL
CREATE TEMP FUNCTION cols_to_rows(root STRING) AS (
  ARRAY(SELECT REPLACE(SPLIT(kv, ':') [OFFSET(1)], '"', '') cols 
  FROM UNNEST(SPLIT(REGEXP_REPLACE(root, r'^{|}$', ''))) kv
  WHERE SPLIT(kv, ':') [OFFSET(0)] != '"id"'
  )
);
WITH `project.dataset.table` AS (
  SELECT 1 id, 'a' col1, 'b' col2, 'c' col3, 'd' col4, 'e' col5 UNION ALL
  SELECT 2 id, 'x', 'y', 'z', 'v', 'w' 
)
SELECT id, col
FROM `project.dataset.table` t,
UNNEST(cols_to_rows(TO_JSON_STRING(t))) col

with result as 

id  col  
1   a    
1   b    
1   c    
1   d    
1   e    
2   x    
2   y    
2   z    
2   v    
2   w

Upvotes: 1

Related Questions