Suppose that you have a table with the fields (some_id, some_text, order_id). Suppose further that you're asked for a query that returns (some_id, all text entries concatenated together here).

Sounds impossible, right? Well here is a pure SQL solution for Oracle (using several non-ANSI extensions, of course):

\nSELECT some_id\n  , SUBSTR(txt, 3) as concatenated_text\nFROM (\n    SELECT some_id\n      , SYS_CONNECT_BY_PATH(some_text, ', ') as txt\n    FROM (\n        SELECT some_id\n          , some_text\n          , RANK() OVER (\n              PARTITION BY some_id\n              ORDER BY order_id\n            ) as id\n          , RANK() OVER (\n              PARTITION BY some_id\n              ORDER BY order_id DESC\n            ) as r\n        FROM some_table\n      )\n    START WITH id = 1\n    CONNECT BY PRIOR id = id - 1\n      AND PRIOR some_id = some_id\n  )\nWHERE r = 1\n

I got the idea for this from [link|http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:229614022562#48352191372887|http://asktom.oracle...62#48352191372887] but working out the details wasn't as simple as I thought it would be. (Plus his formatting was horrible.)

Cheers,
Ben