BigQuery多列转置技术求助：CMPTTR NAME列将从2动态增加至n列

Hey there! Dealing with dynamically expanding columns like your CMPTTR NAME series is a classic BigQuery challenge—static pivot/unpivot won’t cut it here, so we’ll use dynamic SQL with EXECUTE IMMEDIATE to handle any number of columns. Let’s break this down:

Step 1: Automatically Fetch All Dynamic Columns #

First, we need to get a list of all columns matching your CMPTTR NAME pattern. We’ll query BigQuery’s built-in INFORMATION_SCHEMA.COLUMNS to pull this list dynamically, no manual column listing required:

SELECT STRING_AGG(FORMAT('`%s`', column_name), ', ') AS cmp_columns
FROM `your_project.your_dataset.INFORMATION_SCHEMA.COLUMNS`
WHERE table_name = 'your_table'
  AND column_name LIKE 'CMPTTR NAME%'; -- Tweak this pattern to match your exact column naming

This returns a comma-separated string of all your CMPTTR NAME columns, wrapped in backticks to safely handle spaces or special characters in column names.

Step 2: Build & Run the Dynamic Transpose Query #

Next, we’ll use EXECUTE IMMEDIATE to construct and run a transpose query on the fly. The core logic uses UNPIVOT to turn columns into rows, then optionally PIVOT to reshape the data to your desired output structure. Here’s a full working example:

DECLARE cmp_columns STRING;

-- Grab the list of CMPTTR NAME columns
SET cmp_columns = (
  SELECT STRING_AGG(FORMAT('`%s`', column_name), ', ')
  FROM `your_project.your_dataset.INFORMATION_SCHEMA.COLUMNS`
  WHERE table_name = 'your_table'
    AND column_name LIKE 'CMPTTR NAME%'
);

-- Build and execute the dynamic transpose logic
EXECUTE IMMEDIATE FORMAT("""
  SELECT *
  FROM (
    -- Unpivot dynamic columns into row-based format
    SELECT
      your_unique_id, -- Replace with your table's identifier column (e.g., user_id, record_id)
      column_name AS cmpttr_label,
      column_value AS cmpttr_value
    FROM `your_project.your_dataset.your_table`
    UNPIVOT (
      column_value FOR column_name IN (%s)
    )
  )
  -- Optional: Pivot back if you need a column-based output (adjust as needed)
  PIVOT (
    MAX(cmpttr_value) FOR cmpttr_label IN (%s)
  )
""", cmp_columns, cmp_columns);

Quick Tips for Customization #

• Adjust the column pattern: If your columns are named like CMPTTR_1, CMPTTR_2 instead, update the LIKE clause to LIKE 'CMPTTR_%'.
• Skip the pivot: If you only need the unpivoted row-based data, remove the entire PIVOT block from the query.
• Handle data types: If your CMPTTR NAME columns have mixed data types, add a CAST(column_value AS STRING) in the unpivot step to avoid type mismatches.

This approach will automatically adapt to any number of CMPTTR NAME columns—whether it’s 2 today or 100 tomorrow.

内容的提问来源于stack exchange，提问作者Navin