Subject Re: [firebird-support] How does LIST() work?
Author Helen Borrie
Post date 2015-11-12T09:28:23Z
Re: [firebird-support] How does LIST() work? Hello 'Louis,

Thursday, November 12, 2015, 9:03:52 PM, you wrote:


The document is quite slim on this. Is it possible to, for a parent row to retrieve child rows and ‘pack’ a column from the detail rows into a list using the LIST function?

E.g. something like

Select
P.*,
LIST(C.ID)
from PARENT P
join CHILD C on C.PARENT_ID = P.ID

to yield

P_COLUMN_VALUE 1,2,3,4



You could use a correlated subquery:

select d.dept_no,
      (select cast (list(e.emp_no) as varchar(100)) from employee e
       where e.dept_no = d.dept_no) as emplist
from department d

The cast is necessary to get the output in your example, as the LIST() result is a text BLOB.

Helen