9

Does anyone know if Firebird 2.5 has a function similar to the "STUFF" function in SQL?

I have a table which contains parent user records, and another table which contains child user records related to the parent. I'd like to be able to pull a comma delimited string of the "ROLES" the user has without having to use a second query, loop over the values returned for the given ID and create the string myself.

I've searched for any other related questions, but have not found any. The question in this link string equivalent of Sum to concatenate is basically what I want to do too, but with the Firebird 2.5 database.

a_horse_with_no_name
  • 440,273
  • 77
  • 685
  • 758
Phil
  • 3,779
  • 3
  • 51
  • 94

1 Answers1

14

It looks like you are in luck - Firebird 2.1 introduced a LIST() aggregate function which works like GROUP_CONCAT in MySQL, which allows a query like so:

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

Edit, re Ordering

You may be able to influence ordering by pre-ordering the data in a derived table, prior to applying the LIST aggregation function, like so:

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;
Mark Rotteveel
  • 82,132
  • 136
  • 114
  • 158
StuartLC
  • 96,413
  • 17
  • 181
  • 256
  • What about ordering? Let's say I need parents' and childrens' names in alphabetical order, if I add "ORDER BY p.Name, c.Name) I get this error: "Invalid expression in the ORDER BY clause (not contained in either an aggregate function or the GROUP BY clause)" due to the presence of c.Name in the order by clause. Is there a way to solve this problem? – Bozzy Feb 05 '16 at 11:52
  • 1
    @Bozzy you might be able to influence ordering by doing an ordered pre-projection in a derived table. I've updated the answer. – StuartLC Feb 09 '16 at 17:57