Firebird 2.1 List Function

The List function in Firebird 2.1.

This is an aggregate function that returns a string with the field values concatenated using a delimiter. the default delimiter is the comma. So it's similar to the SUM function, but instead of adding numbers, this function concatenate strings. if some of the values is null then the function returns null.

So suppose I have 2 tables, projects and resources, and I know that a project will have a few resources assigned. Now I need to write a report of projects showing their resources assigned.

CREATE TABLE projects(
    project_id INTEGER,
    name string    
);

CREATE TABLE resources(
    project_id integer,
    user_name string
);

Without the LIST function I have to write this query

SELECT 
   p.project_id as id, 
   p.name, 
   r.user_name
FROM 
   projects p INNER JOIN 
   resources r ON p.project_id = r.project_id

my result will be like this

id  name       user_name
-------------------------
1   Project_1  Steve
1   Project_1  John
2   Project_2  Jim
2   Project_2  Ed

Then in the report I will need to group the records with the same project_id so I don't show duplicate projects for each resource assigned.

Now with Firebird 2.1 I can write the query using the LIST function

SELECT 
   p.project_id AS id, 
   p.name, 
   LIST(r.user_name, ', ') AS resources
FROM 
   projects p INNER JOIN 
   resources r ON p.project_id = r.project_id
GROUP BY 
   p.project_id, 
   p.name

my result will be like this

id  name       resources
-----------------------------
1   Project_1  Steve, John
2   Project_2  Jim, Ed

then in my report I only need to show the results of my query, I don't need to create groups.

The list function returns a BLOB data type, so if I need to display the result in a grid I often CAST the result to varchar. The delimiter parameter is not required (I added because I like to add a space after the comma). You can see more details of how to use the LIST function in the Firebird 2.1 Release Notes

This function is also useful in reports when you need details values in the same row as the master data like in reports headers or footers.