Group all Textual Attributes under a Key to a Single Column

Requirement: The requirement is to group all Countries under a Region into a single column. Consider the table below, which has a number of countries under a given Region Id.

Expected Output: The expected output is to group all country names under a region into a single column as shown below,

Resolution: There is a pre-default function in Oracle that supports this known as List Aggregate function.

The syntax of List Aggregate Funtion is as below,

SELECT COLUMN,LISTAGG(COLUMN_NAME, '| ') WITHIN GROUP (ORDER BY COLUMN_NAME DESC) ALIAS_NAME
FROM TABLE
GROUP BY COLUMN_NAME

The query for our solution would look as below,

SELECT REGION_ID,LISTAGG(COUNTRY_NAME, '| ') WITHIN GROUP (ORDER BY REGION_ID DESC) REGION_COUNTRY
FROM COUNTRIES
GROUP BY REGION_ID

List Agg Output