新的起点 新的开始

快乐生活 !

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

项目中有个需求,将关联出来的数据以特定的字段将其他某一自动聚合。例子如下:

 

1    B1
1    B2
1    B3
2    B4
2    B5
3    B6

to

 


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.

 

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

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

posted on 2012-09-26 22:40 advincenting 阅读(2314) 评论(0)  编辑  收藏 所属分类: 数据库相关


只有注册用户登录后才能发表评论。


网站导航:
 

公告

Locations of visitors to this page

导航

<2012年9月>
2627282930311
2345678
9101112131415
16171819202122
23242526272829
30123456

统计

常用链接

留言簿(13)

随笔分类(71)

随笔档案(179)

文章档案(13)

新闻分类

IT人的英语学习网站

JAVA站点

优秀个人博客链接

官网学习站点

生活工作站点

最新随笔

搜索

积分与排名

最新评论

阅读排行榜

评论排行榜