基于SQL中条件的TextJoin之类的函数 [英] TextJoin like function based on a condition in on SQL

问题描述

试图根据条件确定是否可以在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

这篇关于基于SQL中条件的TextJoin之类的函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！