sqlgoogle-bigquerytransposeunpivotdynamicquery

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

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        |
+---------+------------+-----------+-------+----------+
Solution

  • 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')