Try

SELECT UG.UG_ID_GROUP
, UG.UG_NAME
, UG.UG_DESCR
, (
SELECT CAST(LIST(U.U_NAME, ', ') AS VARCHAR(1000))
FROM U_USER U
WHERE U.U_ID_USER IN (
SELECT UAGRP.UAGRP_ID_USER
FROM U_U_AND_GRP UAGRP
WHERE UAGRP.UAGRP_ID_GROUP = UG.UG_ID_GROUP
) U_NAME
) AS USER_LIST
FROM U_GROUP UG
ORDER BY 4 /* or ORDER BY U_NAME /*
FOR UPDATE




2011/3/26 Markus Ostenried <macnoz@...>

> Hey all,
>
> it's been a while since my last post here which means I'm a happy
> Firebird user for many years now - thank you to the community,
> especially the developers and doc writers =)
> Now I just noticed bug when using the LIST() function. My SQL statement is:
>
> SELECT UG.UG_ID_GROUP
> , UG.UG_NAME
> , UG.UG_DESCR
> , (
> SELECT CAST(LIST(U.U_NAME, ', ') AS VARCHAR(1000))
> FROM U_USER U
> WHERE U.U_ID_USER IN (
> SELECT UAGRP.UAGRP_ID_USER
> FROM U_U_AND_GRP UAGRP
> WHERE UAGRP.UAGRP_ID_GROUP = UG.UG_ID_GROUP
> --ORDER BY U.U_NAME /*uncomment this*/
> )
> ) AS USER_LIST
> FROM U_GROUP UG
> FOR UPDATE
>
> This selects all user groups from U_GROUP and sub-selects a list of
> the group members' user names from U_USER. Membership is defined via
> m-to-n table U_U_AND_GRP.
>
> Without the ORDER BY this returns "WM, EM, OZ" and "FI" in column
> USER_LIST (two records in result set). When I uncomment the ORDER BY
> clause then my grid shows nothing in this column. I thought it might
> always return NULL (on Windows XP) but doing this SELECT on another
> machine (Windows Server 2008 R2) I saw a strange value of "ÿÿ"
> returned instead of "WM, EM, OZ".
>
> For now I can live fine without the LIST contents being ordered, I
> just wanted to let you know. I searched the tracker but couldn't find
> this particular bug there.
>
> I get this behavior on
>
> - Windows XP SP3 32Bit with
> - Firebird-2.1.3.18185_0_Win32
> - Firebird-2.1.4.18393_0_Win32
> - Firebird-2.5.0.26074_1_Win32
> (all Super-Server)
>
> - Windows Server 2008 R2 64Bit with
> - Firebird-2.1.3.18185_0_Win32
> (Super-Server).
>
> I'm using Delphi 2007 and IBObjects 4.8.7 to access the database but
> confirmed this with ISQL on Windows XP with Firebird 2.5.0 and
> Firebird 2.1.3.
> See below for my table definitions. Let me know if you need more
> information.
>
> Regards,
> Markus
>
>
> CREATE TABLE U_GROUP (
> UG_ID_GROUP INTEGER NOT NULL
> , UG_NAME VARCHAR( 40 ) NOT NULL
> , UG_DESCR VARCHAR( 300 )
> , CONSTRAINT PK_U_GROUP
> PRIMARY KEY ( UG_ID_GROUP )
> );
>
>
> CREATE TABLE U_USER (
> U_ID_USER INTEGER NOT NULL
> , U_NAME VARCHAR( 40 ) NOT NULL
> , CONSTRAINT PK_U_USER
> PRIMARY KEY ( U_ID_USER )
> , CONSTRAINT UQ_U_NAME
> UNIQUE ( U_NAME )
> );
>
>
> CREATE TABLE U_U_AND_GRP (
> UAGRP_ID_U_AND_GRP INTEGER NOT NULL
> , UAGRP_ID_USER INTEGER NOT NULL
> , UAGRP_ID_GROUP INTEGER NOT NULL
> , CONSTRAINT PK_U_U_AND_GRP
> PRIMARY KEY ( UAGRP_ID_U_AND_GRP )
> , CONSTRAINT UQ_UAGRP_USER_GROUP
> UNIQUE ( UAGRP_ID_USER, UAGRP_ID_GROUP )
> );
>
> ALTER TABLE U_U_AND_GRP
> ADD CONSTRAINT FK_UAGRP_ID_GROUP
> FOREIGN KEY ( UAGRP_ID_GROUP )
> REFERENCES U_GROUP ( UG_ID_GROUP )
> , ADD CONSTRAINT FK_UAGRP_ID_USER
> FOREIGN KEY ( UAGRP_ID_USER )
> REFERENCES U_USER ( U_ID_USER );
>
>
> ------------------------------------
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> Yahoo! Groups Links
>
>
>
>

[Non-text portions of this message have been removed]