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

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

CodePudding user response：

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

CodePudding user response：

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