使用多行数据透视表BigQuery [英] Pivot BigQuery table using multiple rows

问题描述

为了透视我的大查询表，我找到了这段代码

In order to pivot my big query table, I found this code

SELECT 'SELECT id, ' || 
   STRING_AGG(
      'MAX(IF(key = "' || key || '", value, NULL)) as `' || key || '`'
   ) 
   || ' FROM `project.dataset.table` GROUP BY id ORDER BY id'
FROM (
  SELECT key 
  FROM `project.dataset.table`
  GROUP BY key
  ORDER BY key

但是，即使我应用了 EXECUTE IMMEDIATE 函数，它也会返回上述代码的字符串.我在该功能中错过了什么?

But even if I apply EXECUTE IMMEDIATE function, it returns a string of the code above. What did I missed in that function ?

感谢您的帮助

推荐答案

在下面使用

EXECUTE IMMEDIATE(
SELECT 'SELECT id, ' || 
   STRING_AGG(
      'MAX(IF(key = "' || key || '", value, NULL)) as `' || key || '`'
   ) 
   || ' FROM `project.dataset.table` GROUP BY id ORDER BY id'
FROM (
  SELECT key 
  FROM `project.dataset.table`
  GROUP BY key
  ORDER BY key
)  
);

这篇关于使用多行数据透视表BigQuery的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！