SQL statement displayed by a group

The SQL statement table content displayed by a group: create table TEST2 (ID NUMBER, NAME VARCHAR2 (20); insert into TEST2 (ID, NAME) values (1, 'stefanie '); insert into TEST2 (ID, NAME) values (1, 'Google '); insert into TEST2 (ID, NAME) values (1, 'sina'); insert into TEST2 (ID, NAME) values (2, 'baidu'); insert into TEST2 (ID, NAME) values (2, 'Microsoft '); commit; www.2cto.com must be displayed in the following format: id name ---------- -------------------------------------- 1 stefanie, google, sina 2 baidu, microsoft Method 1: Use SYS_CONNECT_BY_PATH. For details, refer http://topic.csdn.net/t/20061220/15/5244199.html SELECT t. *, (SELECT ltrim (MAX (SYS_CONNECT_BY_PATH (name, ','), ',') result from (SELECT id, name, rn, LEAD (rn) OVER (partition by id order by rn) rn1 FROM (SELECT id, name, ROW_NUMBER () OVER (order by id, name DESC) rn FROM TEST2) start with id = t. id AND rn1 is null connect by rn1 = PRIOR rn) value from (select distinct id www.2cto.com FROM TEST2) t Elapsed: 00:00:00. 01 Execution Plan -------------------------------------------------------- Plan hash value: 1207214063 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | minute | 0 | select statement | 2 | 26 | 4 (25) | 00:00:01 | 1 | sort aggregate | 1 | 51 | * 2 | connect by with filtering | * 3 | FILTER | | 4 | COUNT | 5 | VIEW | 5 | 255 | 5 (40) | 00:00:01 | 6 | window sort | 5 | 190 | 5 (40) | 00:00:01 | 7 | VIEW | 5 | 190 | 4 (25) | 00:00:01 | 8 | window sort | 5 | 50 | 4 (25) | 00:00:01 | 9 | table access full | TEST2 | 5 | 50 | 3 (0) | 00:00:01 | * 10 | hash join | 11 | connect by pump | 12 | COUNT | 13 | views | 5 | 255 | 5 (40) | 00:00:01 | 14 | window sort | 5 | 190 | 5 (40) | 00:00:01 | 15 | VIEW | 5 | 190 | 4 (25) | 00:00:01 | 16 | window sort | 5 | 50 | 4 (25) | 00:00:01 | 17 | table access full | TEST2 | 5 | 50 | 3 (0) | 00:00:01 | 18 | COUNT | 19 | VIEW | 5 | 255 | 5 (40) | 00:00:01 | 20 | window sort | 5 | 190 | 5 (40) | 00:00:01 | 21 | VIEW | 5 | 190 | 4 (25) | 00:00:01 | 22 | window sort | 5 | 50 | 4 (25) | 00:00:01 | 23 | table access full | TEST2 | 5 | 50 | 3 (0) | 00:00:01 | 24 | VIEW | 2 | 26 | 4 (25) | 00:00:01 | 25 | hash unique | 2 | 6 | 4 (25) | 00:00:01 | 26 | table access full | TEST2 | 5 | 15 | 3 (0) | 00:00:01 | identified Predicate Information (identified by operation id ): ----------------------------------------------- 2-filter ("ID" =: B1 AND "RN1" is null) 3-filter ("ID" =: B1 AND "RN1" is null) 10-access ("RN1" = NULL) statistics limit 1 recursive cballs 0 db block gets 56 consistent gets 0 physical reads 0 redo size 541 bytes sent via SQL * Net to client 385 bytes encoded ed via SQL * Net from client 2 SQL * Net roundtrips to/from client 21 sorts (memory) www.2cto.com 0 sorts (disk) 2 rows processed Method 2: wmsys. wm_concat select id, wmsys. wm_concat (name) name from test2 group by id; Elapsed: 00:00:00. 00 Execution Plan -------------------------------------------------------- Plan hash value: 3598114683 www.2cto.com success | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | minute | 0 | select statement | 2 | 20 | 4 (25) | 00:00:01 | 1 | sort group by | 2 | 20 | 4 (25) | 00:00:01 | 2 | table access full | TEST2 | 5 | 50 | 3 (0) | 00:00:01 | Statistics defaults 0 recursive cballs 0 db block gets 7 consistent gets 0 physical reads 0 redo size 535 bytes sent via SQL * Net to client 385 bytes received via SQL * Net from client 2 SQL * Net roundtrips to/from client 1 sorts (memory) www.2cto.com 0 sorts (disk) 2 rows processed. wm_concat is the SQL> select wmsys. wm_concat (name) name from test2; NAME ------------------------------------------ stefanie, google, sina, baidu, microsoft
In addition, group by gets the expected results, and the seven logical reads are more efficient than the 56 logical reads above. Author: paololiu