TextJoin like function based on a condition in on SQL

Question

Trying to figure out if it is possible to do a textjoin like function in SQL based on a condition. Right now the only way I can think of doing it is by running a pivot to make the rows of the column and aggregating them that way. I think this is the only way to transpose the data in SQL?

Input This would be a aql table (tbl_fruit) that exists as the image depicts

SELECT * FROM tbl_fruit

Output

Answer 1

Below is for BigQuery Standard SQL (without specifically listing each column, thus in a way that it scales ...)

#standardSQL
select `Group`, string_agg(split(kv, ':')[offset(0)], ', ') output
from `project.dataset.table` t,
unnest(split(translate(to_json_string((select as struct t.* except(`Group`))), '{}"', ''))) kv
where split(kv, ':')[offset(1)] != '0'
group by `Group`    

If to apply to sample data from your question - output is