如何在不使用PIVOT的情况下实现SQL行转列？BigQuery平台下带字符串聚合的转置查询优化需求

Got it, let's get your pivot query working correctly! The issue with your current code is that any_value() only picks a single matching value per group, but you need to aggregate all matching values into a comma-separated string for each source_name column.

Here's the adjusted query for BigQuery: #

SELECT 
  id,
  STRING_AGG(IF(source_name = 'cp', value, NULL), ', ') AS cp,
  STRING_AGG(IF(source_name = 'hi', value, NULL), ', ') AS hi,
  STRING_AGG(IF(source_name = 'li', value, NULL), ', ') AS li,
  STRING_AGG(IF(source_name = 'mi', value, NULL), ', ') AS mi
FROM table_name
GROUP BY id

Why this works: #

• STRING_AGG() is BigQuery's built-in function for concatenating strings from multiple rows into a single string, using the specified delimiter (, in this case).
• The IF() condition filters values to only include those where source_name matches the column we're building. Any non-matching rows return NULL, which STRING_AGG() automatically ignores.
• When there are no matching values for a source_name (like mi for id 1), STRING_AGG() returns NULL—exactly what you need for your target output.

Example Output: #

This query will produce exactly the result you're expecting (note: I adjusted the li value for id 3 to match your source data, since your target output had a discrepancy here):

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