how to dynamically unpivot only those columns with a specific suffix in bigquery

Question

I have a table with a large number of columns (around 900 columns, which makes it unfeasible to individually write all the column names). How can I dynamically unpivot only the columns with the suffix '_next' & '_last' (there are hundreds of such columns)? For example:

TABLE:

+---------+------------+-----------+-------+----------+-----------+
|name     |product_next|upload_last|product|books     |active_next|
+---------+------------+-----------+-------+----------+-----------+
|    alice|       a    |   100     |apple  | 10       | 1         |
|      bob|       b    |    23     |orange | 2        | 0         | 
+---------+------------+-----------+-------+----------+-----------+

FINAL TABLE (after unpivoting):

+---------+------------+-----------+-------+----------+
|name     |metric      |value      |product|books     |
+---------+------------+-----------+-------+----------+
|    alice|product     |   a       |apple  | 10       | 
|      bob|product     |    b      |orange | 2        | 
|    alice|upload      |   100     |apple  | 10       | 
|      bob|upload      |    23     |orange | 2        | 
|    alice|active      |    1      |apple  | 10       | 
|      bob|active      |    0      |orange | 2        |
+---------+------------+-----------+-------+----------+

None of the existing answers address the need to dynamically determine all the fields in the table. Someone came up with a solution to this problem here: https://stackoverflow.com/questions/71386894/dynamic-unpivot-in-bigquery — Brendan Buhmann, Oct 06 '22 at 23:41

score 3 · Answer 1 · answered Mar 18 '22 at 16:50

Consider below approach

select * from your_table
unpivot (metric for col in (product_next, upload_last, active_next))

if applied to sample data in your question

with your_table as (
  select 'alice' name, 'a' product_next, '100' upload_last, 'apple' product, '10' books, '1' active_next union all
  select 'bob', 'b', '23', 'orange', '2', '0'  
)

output is

score 1 · Accepted Answer · answered Mar 18 '22 at 19:14

Additionally, to @Mikhail Answer that is correct you need to add a WHERE clause with a REGEXP_CONTAINS expression as the following one:

where REGEXP_CONTAINS(col, '_next') OR REGEXP_CONTAINS(col,'_last')

The full Query will be:

select * from your_table
unpivot (metric for col in (product_next, upload_last, active_next)) 
where REGEXP_CONTAINS(col, '_next') OR REGEXP_CONTAINS(col,'_last')

how to dynamically unpivot only those columns with a specific suffix in bigquery

2 Answers2