CODEWITHSUNDEEP
sqlgoogle-bigquery
silviacamplani
silviacamplani

Reputation: 361

BigQuery - operations on multiple columns sharing same substring on name

I have several tables with the column names like:

The number of columns sharing the same substring may vary from table to table.

For each table I have to perform queries containing operations such as:

ROW_NUMBER() OVER (PARTITION BY name, surname, description, 
    CAST(FLCOLY01 AS STRING), CAST(FLCOLX01 AS STRING), 
    CAST(FLCOLY02 AS STRING), CAST(FLCOLX02 AS STRING), 
    CAST(FLCOLY03 AS STRING), CAST(FLCOLX03 AS STRING), 
    ...
    CAST(FLCOLYn AS STRING), CAST(FLCOLXn AS STRING) 
    ORDER BY date ASC)

CASE WHEN FLCOLX01  = 125 THEN VALUE_1
         WHEN FLCOLX02  = 125 THEN VALUE_2
         WHEN FLCOLX03 = 125 THEN VALUE_3
         WHEN FLCOLX04 = 125 THEN VALUE_4
         WHEN FLCOLX05 = 125 THEN VALUE_5
         WHEN FLCOLX06 = 125 THEN VALUE_6
         WHEN FLCOLX07 = 125 THEN VALUE_7
         ELSE NULL END AS FLCOLX125

Is there a better way to write these queries?

Upvotes: 0

Views: 78

Answers (1)

David Morales
David Morales

Reputation: 670

There is no alternative, as far as I know, since you want a single output column giving priorities to the different fields.

Your approach is the right one.

Upvotes: 1

Related Questions