基于SQL中条件的TextJoin之类的函数

本文介绍了基于SQL中条件的TextJoin之类的函数的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

试图根据条件确定是否可以在SQL中执行类似于textjoin的函数.现在，我能想到的唯一方法是运行枢轴以制造列的行并以这种方式聚合它们.我认为这是在SQL中转置数据的唯一方法吗?

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?

输入这将是图片所描述的aql表(tbl_fruit)

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

选择*来自tbl_fruit

SELECT * FROM tbl_fruit

输出

推荐答案

以下内容适用于BigQuery Standard SQL(无需专门列出每列，因此可以扩展...)

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