Home » SQL & PL/SQL » SQL & PL/SQL » How can I generate output like Matrix Transposition?
How can I generate output like Matrix Transposition? [message #248186] Thu, 28 June 2007 04:16 Go to next message
fastfreeeasy
Messages: 25
Registered: June 2007
Junior Member
My EMP table having following data:

emp_code emp_dept emp_sal
-------- -------- -------
E0000001 D001 1000000
E0000002 D002 2000000
E0000003 D003 3000000
E0000004 D004 4000000

How can I generate output like (Matrix Transposition) the following:

E0000001 E0000002 E0000003 E0000004
D001 D002 D003 D004
1000000 2000000 3000000 4000000

Can we write a generic SQL or PL/SQL for the above?
Re: How can I generate output like Matrix Transposition? [message #248192 is a reply to message #248186] Thu, 28 June 2007 04:26 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
This is known as a pivot query. But...as a select needs to know what columns you are selecting, you need to know in advance the number of columns you expect. You didn't format your query so I can't tell whether you expect a concatenated result in each row (1 column) or separate columns.

MHE
Re: How can I generate output like Matrix Transposition? [message #248209 is a reply to message #248192] Thu, 28 June 2007 05:11 Go to previous messageGo to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
Hi Maaher!!

i tried to do some thing in pl/sql ..but how can i format the output properly...i can write code in better way if i know how to deal pixel in pl/sql like other language(C,C++).

declare
v_emp varchar2(300) := '';
v_dept varchar2(300) :='';
v_sal varchar2(300) :='';
tmp_dept number;
tmp_sal number;

begin 

for rec in (select EMPNO from emp where rownum<5)
loop
v_emp :=  v_emp|| rec.empno;

 select deptno,sal into tmp_dept,tmp_sal from emp where empno=rec.empno;

v_dept := v_dept||tmp_dept; 
v_sal   := v_sal|| tmp_sal; 
end loop;
dbms_output.put_line(v_emp); 
dbms_output.put_line(v_dept);
dbms_output.put_line(v_sal);
end ;




output is as-:

7369749975217566
20303020
800160012502975

--Yash
Re: How can I generate output like Matrix Transposition? [message #248211 is a reply to message #248209] Thu, 28 June 2007 05:15 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Yash, if you search the board, you'll find a lot of examples for pivot queries.

fastfreeeasy, I'm still waiting.

MHE
Previous Topic: FIRST_VALUE under a subselect problem
Next Topic: how to get the QUARTERLY data
Goto Forum:
  


Current Time: Sat Dec 10 05:02:00 CST 2016

Total time taken to generate the page: 0.04977 seconds