quey

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

you'll need to use PL/SQL to do that ...

Hi Pipo,

Can u write a pl/sql code for that.

paresh

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;
/
------------------------------------------

THANKs PIPO

You have solved my problem

Paresh

you're welcome ;)