row values to column and columns names to values bigquery

user_13071138 0
Viewed 828

i'm trying to create a new table that has two columns 'tags' ,'cnt'

'tags' will contain the columns names as values

'cnt' contain the value per origin column

origin table

Desire table

google-bigquery pivot sql regex
2 Answers

You can unpivot using arrays:

select el.which, el.cnt
from t cross join
     (unnest([struct('ALL_CLOSE' as which, t.all_close as cnt),
              struct('ALL_OPEN' as which, t.all_open as cnt),
              . . .
             ])) u(el)
user_11440350

Below for BigQuery Standard SQL 

#standardSQL
SELECT 
  TRIM(SPLIT(kv, ':')[OFFSET(0)], '"') tag,
  SPLIT(kv, ':')[SAFE_OFFSET(1)] cnt
FROM `project.dataset.table` t,
UNNEST(REGEXP_EXTRACT_ALL(TRIM(TO_JSON_STRING(t), '{}'), r'(.*?)(?:,|$)')) kv

You can test, play with above using sample data from your question as in below example 

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 8279 all_close, 4 all_open, 1504 only_o, 16785 only_f 
)
SELECT 
  TRIM(SPLIT(kv, ':')[OFFSET(0)], '"') tag,
  SPLIT(kv, ':')[SAFE_OFFSET(1)] cnt
FROM `project.dataset.table` t,
UNNEST(REGEXP_EXTRACT_ALL(TRIM(TO_JSON_STRING(t), '{}'), r'(.*?)(?:,|$)')) kv

with result 

Row tag         cnt  
1   all_close   8279     
2   all_open    4    
3   only_o      1504     
4   only_f      16785
user_52219440
Related