我如何在大查询中旋转

Question

说我有数据

id，col1，col2，col3，col4，col5

1，a，b，c，d，e

我希望结果是...

1，a

1，b

1，c

1，d

1，e

我如何在大查询中透视ID？

Answer 1

以下是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  

您可以使用以下虚拟数据进行测试/播放

#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  

结果为

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