Oracle 10g中的聚合字符串连接 [英] Aggregate string connection in Oracle 10g

问题描述

我看到了前面的问题，其中表的列为"No"和"Name"，其他的列为数字列，但无法实现针对我的案例提供的答案.我需要做同样的事情，但是要使用非数字分组.源表是带有以下列的tbl1:

I saw the previous question where the table had the columns "No" and "Name", and others that grouped with numeric columns, but was unable to implement the answers provided for my case. I need to do the same thing, but with non-numeric groupings. The source table is tbl1 with these columns:

POD    Name
---    -----
North  Rony
North  James
North  Aby
South  Sam
South  Willy
West   Mike

我需要进行以下聚合:

POD    Name
---    -----
North  Aby,James,Rony
South  Sam,Willy
West   Mike

由于"POD"是非数字的，因此Msyma，Dinup和chetan以前的解决方案似乎对我不起作用.

Since "POD" is non-numeric, the previous solutions by Msyma, Dinup, and chetan didn't seem to work for me.

我不知道如何将知识从他们的答案转移到这些要求上.

I don't know how to make the knowledge transfer from their answers to these requirements.

理想的查询应该是

SELECT POD, AGGREGATESTRING(Name)
FROM tbl1
GROUP BY POD

在理想的示例中，AGGREGATESTRING不会对人的名字进行排序，但是我认为我可以在需要的地方插入"ORDER BY".

In the ideal example the AGGREGATESTRING doesn't sort the people's names, but I think I'll be able to insert an "ORDER BY" where needed.

推荐答案

又是WM_CONCAT的一种方式

yet one way WM_CONCAT

with Q as
 (select 'North' POD, 'Rony' name
    from DUAL
  union all
  select 'North', 'James'
    from DUAL
  union all
  select 'North', 'Aby'
    from DUAL
  union all
  select 'South', 'Sam'
    from DUAL
  union all
  select 'South', 'Willy'
    from DUAL
  union all
  select 'West', 'Mike' from DUAL)
select pod, to_char(wm_concat(name)) as name from q group by pod

具有分层查询的字符串聚合

string aggregation with hierarchical query

with Q as
 (select 'North' POD, 'Rony' name
    from DUAL
  union all
  select 'North', 'James'
    from DUAL
  union all
  select 'North', 'Aby'
    from DUAL
  union all
  select 'South', 'Sam'
    from DUAL
  union all
  select 'South', 'Willy'
    from DUAL
  union all
  select 'West', 'Mike' from DUAL)
select pod, group_name
  from (select t1.*, level as lv, substr(sys_connect_by_path(name, ','), 2) as group_name
          from (select t1.*, nvl(lag(row_rank) over(partition by pod order by row_rank), 0) as parent_row_rank
                  from (select q.*,
                               rank() over(partition by pod order by name) as row_rank,
                               rank() over(partition by pod order by name desc) as row_rank_desc
                          from q) t1) t1
         where row_rank_desc = 1
        connect by prior row_rank = parent_row_rank
               and prior pod = pod
         start with parent_row_rank = 0) t1

这篇关于Oracle 10g中的聚合字符串连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！