LISTAGG function in DBMS is used to aggregate strings from data in columns in a database table.
- It makes it very easy to concatenate strings. It is similar to concatenation but uses grouping.
- The speciality about this function is that, it also allows to order the elements in the concatenated list.
Syntax:
LISTAGG (measure_expr [, 'delimiter']) WITHIN GROUP (order_by_clause) [OVER query_partition_clause] measure_expr : The column or expression to concatenate the values. delimiter : Character in between each measure_expr, which is by default a comma (,) . order_by_clause : Order of the concatenated values.
Let us have a table named Gfg having two columns showing the subject names and subject number that each subject belongs to, as shown below :
SQL> select * from GfG; SUBNO SUBNAME ---------- ------------------------------ D20 Algorithm D30 DataStructure D30 C D20 C++ D30 Python D30 DBMS D10 LinkedList D20 Matrix D10 String D30 Graph D20 Tree 11 rows selected.
Query 1: Write an SQL query using LISTAGG function to output the subject names in a single field with the values comma delimited.
SQL> SELECT LISTAGG(SubName, ' , ') WITHIN GROUP (ORDER BY SubName) AS SUBJECTS 2 FROM GfG ;
Output:
SUBJECTS ----------------------------------------------------------------------------------- Algorithm , C , C++ , DBMS , DataStructure , Graph , LinkedList , Matrix , Python , String , Tree
Query 2: Write an SQL query to group each subject and show each subject in its respective department separated by comma with the help of LISTAGG function.
SQL> SELECT SubNo, LISTAGG(SubName, ' , ') WITHIN GROUP (ORDER BY SubName) AS SUBJECTS 2 FROM GfG 3 GROUP BY SubNo;
Output:
SUBNO SUBJECTS ------ -------------------------------------------------------------------------------- D10 LinkedList , String D20 Algorithm , C++ , Matrix , Tree D30 C , DBMS , DataStructure , Graph , Python
Query 3: Write an SQL query to show the subjects belonging to each department ordered by the subject number (SUBNO) with the help of LISTAGG function.
SQL> SELECT SubNo, LISTAGG(SubName, ',') WITHIN GROUP (ORDER BY SubName) AS SUBJECTS 2 FROM GfG 3 GROUP BY SubNo 4 ORDER BY SubNo;
Output:
SUBNO SUBJECTS ----- -------------------------------- D10 LinkedList, String D20 Algorithm, C++, Matrix, Tree D30 C, DBMS, DataStructure, Graph, Python