get column names in bigquery based on the column value

Question

I am trying to get the column names when we only know the column values.

So lets say I have the above table and I want to write a big query code to get the all column names which contains Sam

Can anybody pls help me?

Thanks in advance

Answer 1

Consider below simple solution

select arr[safe_offset(1)] value, 
  string_agg(arr[offset(0)], ', ') columns
from your_table t,
unnest(regexp_extract_all(to_json_string(t), r'"\w+":"\w+"')) kv,
unnest([struct(split(trim(kv, '"'), '":"') as arr)])
group by value       

if applied to sample data in your question - output is

or you can use below if you want only specific names

select arr[safe_offset(1)] value, 
  string_agg(arr[offset(0)], ', ') columns
from your_table t,
unnest(regexp_extract_all(to_json_string(t), r'"\w+":"\w+"')) kv,
unnest([struct(split(trim(kv, '"'), '":"') as arr)])
group by value
having value in ('Sam', 'Mac')

with output