You could of course do something like

SELECT N.NAMEID, N.NAME, M1.MEMBERNAME, M2.MEMBERNAME, M3.MEMBERNAME
FROM NAMES N
LEFT JOIN MEMBERS M1 ON N.NAMEID = M1.NAMEID
AND NOT EXISTS (SELECT * FROM MEMBERS M1A
WHERE M1.NAMEID = M1A.NAMEID
AND M1.MEMBERID > M1A.MEMBERID)
LEFT JOIN MEMBERS M2 ON M1.NAMEID = M2.NAMEID
AND NOT EXISTS (SELECT * FROM MEMBERS M2A
WHERE M2.NAMEID = M2A.NAMEID
AND M1.MEMBERID < M2A.MEMBERID
AND M2.MEMBERID > M2A.MEMBERID)
LEFT JOIN MEMBERS M3 ON M2.NAMEID = M3.NAMEID
AND NOT EXISTS (SELECT * FROM MEMBERS M3A
WHERE M3.NAMEID = M3A.NAMEID
AND M2.MEMBERID < M3A.MEMBERID
AND M3.MEMBERID > M3A.MEMBERID)

(add more LEFT JOINs as required)

For this to work, you would need to know the maximum number of rows to 'flatten out' - or just make a decision like: 'I'll return up to xx membernames'. The statement would be slightly simpler to write if you had an additional column in MEMBERS that were sequential for each NAMEID (you could compare to a constant rather than use NOT EXISTS), but then it would be more complex with concurrency and when deleting rows. Moreover, it wouldn't help if you had a WHERE clause that eliminated some of the members. Hence, I add such a column only with static tables (adding such a field then helps me reducing the length and complexity of my SQL statement).

HTH,
Set


-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Zd
Sent: 24. februar 2008 20:54
To: firebird-support@yahoogroups.com
Subject: [firebird-support] How to join multiple rows into a single string in FB?

Dear Group,

I need your help with the following issue:

I have one table which stores names and ids like:

TABLE names
NameID, Name, CreationDate etc.

and I have the MEMBERS table which stores multiple membernames attached to a single name like

TABLE members
NameID, MemberID, MemberName etc.

I'd like a query, which returns multiple names with the given IDs and all the membernames that are attached to it.

How do I write an SQL for this? So the result of the query should be like:
NameID1, Name1, 'MemberName1, MemberName2, MemberName3, etc.'
NameID2, Name2, 'MemberName4, MemberName5, etc.'
...

Thanks for your help!

Zd