如何将一对多关系数据转换为指定逗号分隔格式

现有两张存在一对多关系的动态表（父表user、子表books），数据可持续新增，表结构如下：

父表user

子表books

需求输出格式如下：

'author1' AS author1,'x' AS x,'y' AS y,'author2' AS author2,'z' AS z,'author3' AS author3,'z1' AS z1

目前已实现父表数据的转换，SQL语句如下：

select listagg('''' || UserName || ''' as "' || UserName || '"', ',') within group (order by Aid)
from   (select distinct Aid,UserName  from user order by Aid);

求如何实现包含子表数据的完整转换。

可以通过合并父表用户记录与子表书籍记录，统一处理后再用listagg拼接，具体SQL如下：

select listagg(concat('''', item, ''' AS ', item), ',') within group (order by aid_seq, item_type)
from (
    -- 提取父表用户记录，标记类型为1（确保用户排在对应书籍前）
    select 
        aid as aid_seq,
        1 as item_type,
        username as item
    from user
    union all
    -- 提取子表书籍记录，标记类型为2
    select 
        b.aid as aid_seq,
        2 as item_type,
        b.name as item
    from books b
    join user u on b.aid = u.aid
) combined;

逻辑说明 #

1. 合并数据集：用union all将父表用户名和子表书籍名称合并到同一字段item，通过aid_seq关联用户与对应书籍，item_type确保用户记录排在自身书籍记录之前。
2. 格式化单个条目：用concat把每个item转换为需求的'xxx' AS xxx格式。
3. 拼接最终结果：通过listagg按用户ID顺序、类型顺序拼接所有格式化后的字符串，得到符合要求的输出。

注意事项 #

• 若user表中存在无对应书籍的用户（如author4），该用户的记录仍会被包含在结果中。
• 排序规则order by aid_seq, item_type可严格保证每个用户紧跟自己的所有书籍，与需求格式顺序一致。

内容的提问来源于stack exchange，提问作者bless