CODEWITHSUNDEEP
google-bigquery
Johnpl99
Johnpl99

Reputation: 47

BigQuery - Concatenate multiple columns into a single column for large numbers of columns

I have data that looks like:

row col1 col2 col3 ... coln
1 A null B ... null
2 null B C ... D
3 null null null ... A

I want to condense the columns together to get:

row final
1 A, B
2 B, C, D
3 A

The order of the letters doesn't matter, and if the solution includes the nulls eg. A,null,B,null ect. I can work out how to remove them later. I've used up to coln as I have about 200 columns to condense.

I've tried a few things and if I were trying to condense rows I could use STRING_AGG() example

Additionally I could do this:

SELECT 
CONCAT(col1,", ",col2,", ",col3,", ",coln) #ect.
 FROM mytable

However, this would involve writing out each column name by hand which isn't really feasible. Is there a better way to achieve this ideally for the whole table.

Additionally CONCAT returns NULL if any value is NULL.

Upvotes: 1

Views: 4435

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

#standardSQL
select row, 
  (select string_agg(col, ', ' order by offset)
  from unnest(split(trim(format('%t', (select as struct t.* except(row))), '()'), ', ')) col with offset
  where not upper(col) = 'NULL'
  ) as final
from `project.dataset.table` t

if to apply to sample data in your question - output is

enter image description here

Upvotes: 2

Yun Zhang
Yun Zhang

Reputation: 5503

Not in exact format that you asked for, but you can try if this simplifies things for you:

SELECT TO_JSON_STRING(mytable) FROM mytable

If you want the exact format, you can write a regex to extract values from the output JSON string.

Upvotes: 0

Related Questions