Firebird 2.5 - Equivalent STUFF function in SQL (GROUP_CONCAT in MySql / LISTAGG in Oracle)

Question

Does anyone know if there is a Firebird 2.5 function similar to the "STUFF" function in SQL? I have a table that contains the parent records of the user, and another table contains the records of the child users related to the parent. I would like to be able to cut out the comma-separated string from "ROLES" that the user has without using a second query, iterate over the values ​​returned for this identifier, and create the string myself.

I searched for any other related questions, but could not find. The question in this link is the string equivalent of Sum for concatenation - this is basically what I want to do too, but with the Firebird 2.5 database.

Answer 1

You seem lucky - Firebird 2.1 has a LIST() aggregate function that works like GROUP_CONCAT in MySql, which allows this query:

 SELECT p.Name, LIST(c.Name, ', ') FROM parent p INNER JOIN child c on c.parentid = p.parentid GROUP by p.Name; 

Change, reorder

You may be able to influence the ordering by first ordering the data in the view before applying the LIST aggregation function, for example:

 SELECT x.ParentName, LIST(x.ChildName, ', ') FROM ( SELECT p.Name as ParentName, c.Name as ChildName FROM parent p INNER JOIN child c on c.parentid = p.parentid ORDER BY c.Name DESC ) x GROUP by x.ParentName;