Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

SQL - Multiple Fetch into Single Column with Comma Separator

733880Nov 14 2011 — edited Nov 14 2011
Hello Experts,
Good Day to all...

I need your help on following scenarios. The below query returns set of titleID strings. Instead of printing them one below the other as query output, I want the output to be in batch of 25 values.i.e each row should have 25 values separated by comma. i.e If there are 100 titles satisfying the output, then there should be only four rows with and each row having 25 titles in comma separated manner.
SELECT DISTINCT title_id
           FROM pack_relation
          WHERE package_id IN (      SELECT DISTINCT fa.package_id
                                                FROM annotation fa
                                            GROUP BY fa.package_id
                                              HAVING COUNT
                                                        (fa.package_id) <100);
I tried with the PL/SQL block; whereas it is printing all the values continously :(
I need to stop with 25 values and display.

If its possible with SQL block alone; then it would be of great help

											
														
DECLARE
   v_str   VARCHAR2 (32767)  := NULL;

   CURSOR c1
   IS
     SELECT DISTINCT title_id
           FROM pack_relation
          WHERE package_id IN (      SELECT DISTINCT fa.package_id
                                                FROM annotation fa
                                            GROUP BY fa.package_id
                                              HAVING COUNT
                                                        (fa.package_id) <100);
BEGIN
   FOR i IN c1
   LOOP
      v_str := v_str || ',' || i.title_id;
   END LOOP;
   v_str := SUBSTR (v_str, 2);
   DBMS_OUTPUT.put_line (v_str);
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Error-->' || SQLERRM);
END;
Thanks...
This post has been answered by bpat on Nov 14 2011
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 12 2011
Added on Nov 14 2011
7 comments
1,975 views