Converting column data into row in pl/sql

Question

Hi When i went for an interview they asked me this question.

    Create table course(Name CHAR(10));

    insert into course values ('Java');
    insert into course values ('Oracle');
    insert into course values ('Python');
    insert into course values ('C');
    insert into course values ('C++');

     o/p:
        Java Oracle python c c++

Thanks in advance, Sandhya.

Answer 1

I presume that NAME column's datatype should have been VARCHAR2, not CHAR.

Anyway, another option (similar to Tejash's LISTAGG) which uses XMLAGG and is safer if the result is larger than 4000 characters.

SQL> SELECT RTRIM (
  2            XMLAGG (XMLELEMENT (e, name || ' ') ORDER BY null).EXTRACT (
  3               '//text()'),
  4            ',')
  5            result
  6  FROM course;

RESULT
------------------------------------------------------------
Java       Oracle     Python     C          C++

SQL>

---

Or, as you tagged the question with PL/SQL tag, then an anonymous PL/SQL block might look like this:

SQL> set serveroutput on
SQL>
SQL> declare
  2    l_result varchar2(100);
  3  begin
  4    for cur_r in (select name from course) loop
  5      l_result := l_result ||' '|| cur_r.name;
  6    end loop;
  7
  8    dbms_output.put_line(trim(l_result));
  9  end;
 10  /
Java       Oracle     Python     C          C++

PL/SQL procedure successfully completed.

SQL>

---

Or - similarly - a function:

SQL> create or replace function f_course
  2    return varchar2
  3  is
  4    l_result varchar2(100);
  5  begin
  6    for cur_r in (select name from course) loop
  7      l_result := l_result ||' '|| cur_r.name;
  8    end loop;
  9
 10    return trim(l_result);
 11  end;
 12  /

Function created.

SQL> select f_course from dual;

F_COURSE
--------------------------------------------------------------
Java       Oracle     Python     C          C++

SQL>

---

Or, a procedure with an OUT parameter:

SQL> create or replace procedure p_course (par_result out varchar2)
  2  is
  3    l_result varchar2(100);
  4  begin
  5    for cur_r in (select name from course) loop
  6      l_result := l_result ||' '|| cur_r.name;
  7    end loop;
  8
  9    par_result := trim(l_result);
 10  end;
 11  /

Procedure created.

SQL> declare
  2    l_out varchar2(100);
  3  begin
  4    p_course(l_out);
  5    dbms_output.put_line(l_out);
  6  end;
  7  /
Java       Oracle     Python     C          C++

PL/SQL procedure successfully completed.

SQL>

---

As you can see, quite a few options; use the one that most suits your needs.

Answer 2

You can use an aggregate function - LISTAGG as following:

SQL> SELECT
  2      LISTAGG(TRIM(NAME), ' ') WITHIN GROUP(
  3          ORDER BY
  4              NULL
  5      ) AS RESULT
  6  FROM
  7      COURSE;

RESULT
--------------------------------------------------------------------------------
C C++ Java Oracle Python

SQL>

Cheers!!