将所有行列合并到联接表中的单个列中 [英] merge all rows columns into single column from joined table

问题描述

我想加入一个具有多行的表，并且需要将所有行中的一列合并为单列.

I want to join a table which has multiple rows and need to merge one of the column from all the rows into single column.

select a.parent_id,a.parent_name,concat(b.child_name) from parent a 
join children b on (a.parent_id=b.parent_id);

这应返回所有父行，并且每个父行应具有其所有子行.

This should return all the parent rows and each parent row should have all its children's.

我正在考虑与parent_id分组，但获得多个记录(每个孩子一个记录).除了分组以在单行中获得父母的所有孩子的子女之外，我在这里可以实现什么逻辑.

i am thinking to group with parent_id but getting multiple records (one record per child). What logic i can implement here apart from grouping to get all child's for a parent in single row.

推荐答案

SELECT a.parent_id, a.parent_name, STRING_AGG(b.child_name, ',') as Children
FROM
    Parent a
    INNER JOIN children b
    ON a.Id = b.ParentId
GROUP BY
    a.parent_id
    ,a.parent_name

这篇关于将所有行列合并到联接表中的单个列中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！