轉載地址：

列轉行聚合的簡單實現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 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

自己試驗的例子：

select grantee,wm_concat(granted_role) as grante_role from dba_role_prives group by grantee;

列轉行聚合的簡單實現ORACLE WM_CONCAT LISTAGG函式

相關文章