Introduction to using the wm_concat (column) function in Oracle, oraclewm_concat

Introduction to using the wm_concat (column) function in Oracle, oraclewm_concat

Merge Fields

Use the oraclewm_concat (column) function to merge fields, for example: shopping into u_id goods num ____________________________________________ 1 Apple 2 2 pears 5 1 watermelon 4 3 grape 1 3 Banana 1 1 orange 3 ============== ====================================== the expected result is: -------------------------------------- u_id goods_sum ____________________________________________ 1 Apple, watermelon, orange 2 pears 3 grapes, banana selection u_id, wmsys. wm_concat (goods) goods_sum from shopping group by u_id resolution: After wm_concat is used, values are automatically separated by commas. You can use the replace function to replace commas with other symbols, for example: select replace (wm_concat (goods), '|') from shopping;

Use with |

Expected results: ---------------------------------------- u_id goods_sum _________________________________________ 1 Apple (2 kg), watermelon (4 kg), orange (3 kg) 2 Pears (5 kg) 3 grape (1 kg ), banana (1 kg) -------------------------------------- using the oracle wm_concat (column) function implementation: select u_id, wmsys. wm_concat (goods | '(' | num | 'jin) ') goods_sum from shopping group by u_id

Here is an introduction to the connector |: Click to open the link.

Extension

Case: I want to write a view, similar to create or replace view as select Field 1, Field 2... the field 50 from tablename base table has more than 50 fields. It is too difficult to manually write the fields. Is there any simple method, see if I apply wm_concat to make this requirement simple select 'create or replace view as select' | wm_concat (column_name) | 'from dept' from user_tab_columns where table_name = 'dept ';