Simple implementation of column-to-row aggregation ORACLE WM_CONCAT listparts Function

The simple implementation of column-to-row aggregation is required in the ORACLE WM_CONCAT listparts function project. The associated data is automatically aggregated by a specific field. Example: 1 B11 B21 B32 B42 B53 B6to www.2cto.com 1 B1, B2, B32 B4, B53 B6 get it and think of using the stored procedure. In fact, there is a simpler way. This is the string aggregate function provided by 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 listmediaanalystic Function in 11g Release 2 The listmediaanalytic 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 shoshould use this function for string aggregation. example: create table "testbench" ("A" VARCHAR2 (20 BYTE), "B" VARCHAR2 (20 BYTE) insert into "testbench" (A, B) VALUES ('1', 'b1 ') insert into "testes'" (A, B) VALUES ('1', 'b2') insert into "testes'" (A, B) VALUES ('1', 'b3') insert into "testes'" (A, B) VALUES ('2', 'b4 ') insert into "testes'" (A, B) VALUES ('2', 'b5 ') insert into "testes'" (A, B) VALUES ('3', 'b6 ') select * from test100001 B11 B21 B32 B42 B53 B6 www.2cto.com select a, wm_concat (B) as using inams from testbench group by a order by a select a, listbench (B ,',') within group (order by a) as B from testbench group by 1 B1, B2, B32 B4, B53 B6