Show multiple rows in one row in Oracle PL-SQL

The following can be used to show multiple rows in a single separated by '&' and concatenated.

DECLARE

   CURSOR CUR

   IS

      SELECT COL

        FROM TEST;

   D     CUR%ROWTYPE;

   RES   VARCHAR2 (200);

BEGIN

   OPEN CUR;

   LOOP

      FETCH CUR INTO D;

      EXIT WHEN CUR%NOTFOUND;

      RES := RES || ' & ' || D.COL;

   END LOOP;




   CLOSE CUR;

   DBMS_OUTPUT.PUT_LINE(SUBSTR (RES, INSTR (RES, '&') + 2));

END;

Please leave comment for any query.