Bigquery 中的动态 UnPivot

我想使用 bigquery 脚本动态地将列反转为行。 我正在尝试复制我们所拥有的here https://towardsdatascience.com/pivot-in-bigquery-4eefde28b3be但使用 upivot 运算符。

The table looks like this

我想把它改成这样。

我正在使用这个 sql 脚本，但它没有在 bigquery 中提供所需的输出。请协助。

    DECLARE myunpivot STRING;
SET myunpivot = (
  SELECT CONCAT('("', STRING_AGG(DISTINCT column_name, '", "'), '")'),
From(
SELECT column_name FROM Project_Health.INFORMATION_SCHEMA.COLUMNS
where table_name ="FY22_Operational_PH_Calc_Summary" 
and column_name not in("Subject")));

EXECUTE IMMEDIATE format("""
SELECT * FROM
(
  SELECT * FROM Project_Health.FY22_Operational_PH_Calc_Summary`
)
unpivot
(
  Rating 
  FOR Period in %s
)
""", myunpivot);

When this is run I get

我在第一份 10MB 工作中得到的结果是

("Jan_Baseline_thresholds", "Week_1", "Week_2", "Week_3", "Week_4", "Jan_Avg", "Feb_Baseline_thresholds", "Week_5", "Week_6", "Week_7", "Week_8", "Feb_Avg", "Mar_Baseline_thresholds", "Week_9", "Week_10", "Week_11", "Week_12", "Week_13", "Week_14", "Week_15", "Week_16", "Week_17", "Week_18", "Week_19", "Week_20", "Week_21", "Week_22", "Week_23", "Week_24", "Week_25", "Week_26", "Week_27", "Week_28", "Week_29", "Week_30", "Week_31", "Week_32", "Week_33", "Week_34", "Week_35", "Week_36", "Week_37", "Week_38", "Week_39", "Week_40", "Week_41", "Week_42", "Week_43", "Week_44", "Week_45", "Week_46", "Week_47", "Week_48", "Week_49", "Week_50", "Week_51", "Week_52")

虽然我从第二份工作得到的结果是

SELECT * FROM
(
  SELECT * FROM Project_Health.FY22_Operational_PH_Calc_Summary`
)
unpivot
(
  Rating 
  FOR Period in ("Jan_Baseline_thresholds", "Week_1", "Week_2", "Week_3", "Week_4", "Jan_Avg", "Feb_Baseline_thresholds", "Week_5", "Week_6", "Week_7", "Week_8", "Feb_Avg", "Mar_Baseline_thresholds", "Week_9", "Week_10", "Week_11", "Week_12", "Week_13", "Week_14", "Week_15", "Week_16", "Week_17", "Week_18", "Week_19", "Week_20", "Week_21", "Week_22", "Week_23", "Week_24", "Week_25", "Week_26", "Week_27", "Week_28", "Week_29", "Week_30", "Week_31", "Week_32", "Week_33", "Week_34", "Week_35", "Week_36", "Week_37", "Week_38", "Week_39", "Week_40", "Week_41", "Week_42", "Week_43", "Week_44", "Week_45", "Week_46", "Week_47", "Week_48", "Week_49", "Week_50", "Week_51", "Week_52")
)  
 

我找到了解决方案，问题是连接线

CONCAT('("', STRING_AGG(DISTINCT column_name, '", "'), '")'),

替换为

CONCAT('(', STRING_AGG( column_name, ','), ')'),

以下是完整的查询

DECLARE myunpivot STRING;
SET myunpivot = (
  SELECT CONCAT('(', STRING_AGG( column_name, ','), ')'),
From(
SELECT column_name FROM Project_Health.INFORMATION_SCHEMA.COLUMNS
where table_name ="FY22_Operational_PH_Calc_Summary" 
and column_name not in("Subject")));

EXECUTE IMMEDIATE format("""
SELECT * FROM
(
  SELECT * FROM Project_Health.FY22_Operational_PH_Calc_Summary`
)
unpivot
(
  Rating 
  FOR Period in %s
)
""", myunpivot);