SELECT a.script_id,
a.title,
b.outgoing_note_id,
b.sub_date,
c.contact_name,
( SELECT SUBSTR (SYS_CONNECT_BY_PATH (user_id, ' / '), 4) csv
FROM (SELECT sub_by,
ROW_NUMBER () OVER (ORDER BY user_id) rn,
COUNT (*) OVER () cnt
FROM d
WHERE b.outgoing_note_id = d.outgoing_note_id)
WHERE rn = cnt
START WITH rn = 1
CONNECT BY rn = PRIOR rn + 1)
AS "SUB_BY"
FROM b, c, a
WHERE b.outgoing_note_id = c.outgoing_note_id AND a.script_id =
b.script_id
ORDER BY a.script_id, b.sub_date DESC, b.outgoing_note_id DESC;
On Mon, May 9, 2011 at 11:26 AM, Michael Moore <michaeljmo...@gmail.com>wrote:
> delete the comma after "BY_SUB" and delete the AND before ORDER BY.
>
> Mike
>
>
> On Mon, May 9, 2011 at 11:01 AM, chintan salvi <chintu4...@gmail.com>wrote:
>
>> Hi Joel thnx for ur reply. But I am using 9i so* /*listagg is an 11g
>> built-in function*/ *it wont work.
>> Hi ddf its not homework I tried the above steps mentioned in my query and
>> since I was not able to figure out how to do that column to comma separated
>> part that's why I posted to get any suggestion on same. But at the same time
>> I already applied a function using cursor and fetching data into it and then
>> in that making it comma separated and return the output.
>> But I would appreciate if I get a solution without using function purely
>> through query.
>>
>> --
>> You received this message because you are subscribed to the Google
>> Groups "Oracle PL/SQL" group.
>> To post to this group, send email to Oracle-PLSQL@googlegroups.com
>> To unsubscribe from this group, send email to
>> oracle-plsql-unsubscr...@googlegroups.com
>> For more options, visit this group at
>> http://groups.google.com/group/Oracle-PLSQL?hl=en
>>
>
>
--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle-PLSQL@googlegroups.com
To unsubscribe from this group, send email to
oracle-plsql-unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
