ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 列转行聚合的简单实现ORACLE WM_CONCAT LISTAGG函数

列转行聚合的简单实现ORACLE WM_CONCAT LISTAGG函数

原创 Linux操作系统 作者:germany006 时间:2013-08-19 10:22:13 0 删除 编辑
转载地址:http://www.2cto.com/database/201209/157913.html

列转行聚合的简单实现ORACLE WM_CONCAT LISTAGG函数
 
项目中有个需求,将关联出来的数据以特定的字段将其他某一自动聚合。例子如下:
 
1    B1
1    B2
1    B3
2    B4
2    B5
3    B6
to
  www.2cto.com  
1    B1,B2,B3
2    B4,B5
3    B6
 
  拿到手就想到用存储过程,其实还有更简单的方式。那就是oracle 提供的字符串聚合函数。
 
WM_CONCAT Built-in Function (Not Supported)
 
If you are not running 11g Release 2, but are running a version of the database where the WM_CONCAT function is present, then it is a zero effort solution as it performs the aggregation for you. It is actually an example of a user defined aggregate function described below, but Oracle have done all the work for you.
  www.2cto.com  
LISTAGG Analystic Function in 11g Release 2
 
The LISTAGG analytic function was introduced in Oracle 11g Release 2, making it very easy to aggregate strings. The nice thing about this function is it also allows us to order the elements in the concatenated list. If you are using 11g Release 2 you should use this function for string aggregation.
 
Example 如下:
 
CREATE TABLE  "TESTAGG"
  (
    "A" VARCHAR2(20 BYTE),
    "B" VARCHAR2(20 BYTE)
  )
 
INSERT INTO "TESTAGG" (A, B) VALUES ('1', 'B1')
INSERT INTO "TESTAGG" (A, B) VALUES ('1', 'B2')
INSERT INTO "TESTAGG" (A, B) VALUES ('1', 'B3')
INSERT INTO "TESTAGG" (A, B) VALUES ('2', 'B4')
INSERT INTO "TESTAGG" (A, B) VALUES ('2', 'B5')
INSERT INTO "TESTAGG" (A, B) VALUES ('3', 'B6')
 
select * from testAgg
 
1    B1
1    B2
1    B3
2    B4
2    B5
3    B6
  www.2cto.com  
select  a  ,wm_concat (b) as policinams from testagg  group by a order by a
 
select  a ,LISTAGG(b,',' ) within group(order by a) as b from testagg group by
 
1    B1,B2,B3
2    B4,B5
3    B6



自己试验的例子:
select grantee,wm_concat(granted_role) as grante_role from dba_role_prives group by grantee;

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28371090/viewspace-768771/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
ORACLE,MYSQL,POSTGRESQL,SQLSERVER

注册时间:2013-03-06

  • 博文量
    766
  • 访问量
    921929