DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: quey

  1. #1
    Join Date
    May 2001
    Location
    London
    Posts
    149
    Hi DBAs,

    I have table emp
    when I EXECUTE FOLL QUERY
    SQL>select * from emp;
    empno ename sal deptno
    1000 JAMES 1000 10
    1001 BLACK 5000 30
    1005 SAM 7000 20

    It is showing rows in horizontal format
    I want the o/p should display

    1000 1001 1005
    JAMES BLACK SAM
    1000 5000 7000
    10 30 20


    WILL I GET SUCH O/P????
    WAITING FOR REPLY.

    Paresh

  2. #2
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    you'll need to use PL/SQL to do that ...

  3. #3
    Join Date
    May 2001
    Location
    London
    Posts
    149
    Hi Pipo,

    Can u write a pl/sql code for that.

    paresh


  4. #4
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    something like that (not tested)PS : be careful that if you have too many lines, you won't be able to do that since your variables (which are varchar), cannot be longer than 4000 characters ...

    ------------------------------------------
    set serveroutput on

    declare
    cursor cur is
    select empno, ename, sal, deptno
    from emp;

    var_empno varchar2(4000) := '';
    var_ename varchar2(4000) := '';
    var_sal varchar2(4000) := '';
    var_deptno varchar2(4000) := '';

    begin
    dbms_output.enable(100000);
    for v_cur in cur
    loop
    var_empno := var_empno || ' ' || v_cur.empno;
    var_ename := var_ename || ' ' || v_cur.ename;
    var_sal := var_sal || ' ' || v_cur.sal;
    var_deptno := var_deptno || ' ' || v_cur.deptno;
    end loop;
    dbms_output.put_line(var_empno);
    dbms_output.put_line(var_ename);
    dbms_output.put_line(var_sal);
    dbms_output.put_line(var_deptno);
    end;
    /
    ------------------------------------------

  5. #5
    Join Date
    May 2001
    Location
    London
    Posts
    149
    THANKs PIPO

    You have solved my problem

    Paresh

  6. #6
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809

    Talking

    you're welcome

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width