Home » SQL & PL/SQL » SQL & PL/SQL » Data display from table (Oracle 9i)
Data display from table [message #409829] |
Wed, 24 June 2009 03:42  |
souvik_roy
Messages: 16 Registered: June 2008
|
Junior Member |
|
|
Hi,
I have two table emp and dep.
there is a common column depno.
Now I want to display the emp_name wrt to the department no.
It should be as follows:
Dep_10 Dep_20 Dep_30
------ ------ -------
A C D
K M J
E null F
G null null
--------------------------
I have used a query as below:
select decode(depno,10,empname) dep_10,decode(depno,20,empname) dep_20,decode(depno,30,empname) dep_30 from emp;
It gives me results as:
Dep_10 Dep_20 Dep_30
-----------------------
A null null
K null null
null C null
null M null
E null D
null null J
G null null
null null F
--------------------------
thre are some unwanted NULL values exist in the data set.
Could anyone tell me if it is possible to have my desired result set and how.
Thanks in advance..
Souvik,
|
|
|
|
Re: Data display from table [message #409865 is a reply to message #409831] |
Wed, 24 June 2009 04:55   |
souvik_roy
Messages: 16 Registered: June 2008
|
Junior Member |
|
|
Hi Michel,
This is the table insert on emp:
Insert into EMP1
(EMPID, EMPNAME, DEPNO)
Values
(100, 'A', 10);
Insert into EMP1
(EMPID, EMPNAME, DEPNO)
Values
(101, 'K', 10);
Insert into EMP1
(EMPID, EMPNAME, DEPNO)
Values
(102, 'C', 20);
Insert into EMP1
(EMPID, EMPNAME, DEPNO)
Values
(103, 'M', 20);
Insert into EMP1
(EMPID, EMPNAME, DEPNO)
Values
(104, 'E', 10);
Insert into EMP1
(EMPID, EMPNAME, DEPNO)
Values
(105, 'D', 30);
Insert into EMP1
(EMPID, EMPNAME, DEPNO)
Values
(106, 'J', 30);
Insert into EMP1
(EMPID, EMPNAME, DEPNO)
Values
(107, 'G', 10);
Insert into EMP1
(EMPID, EMPNAME, DEPNO)
Values
(108, 'F', 30);
COMMIT;
The following is the sql I have used:
SELECT ROWNUM,decode(depno,10,empname) dep_10,
decode(depno,20,empname) dep_20,decode(depno,30,empname) dep_30
from emp1
Thanks..
Souvik
[Updated on: Wed, 24 June 2009 06:13] by Moderator Report message to a moderator
|
|
|
|
|
|
|
|
Re: Data display from table [message #410153 is a reply to message #409970] |
Thu, 25 June 2009 08:37   |
souvik_roy
Messages: 16 Registered: June 2008
|
Junior Member |
|
|
Michel,
I face some issue with row_number() function as what could be the order by clause or partition by clause.
I tried to modify the query as :
select decode(depno,10,empname) dep_10,decode(depno,20,empname) dep_20,decode(depno,30,empname) dep_30 from emp1
minus
select null,null,null from dual;
so that the result set is now adjascent but not merged.
The out put is as:
DEP_10 DEP_20 DEP_30
A
E
G
K
C
M
D
F
J
Regards,
Souvik.
|
|
|
Re: Data display from table [message #410159 is a reply to message #410153] |
Thu, 25 June 2009 08:54   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Quote: |
I face some issue with row_number() function as what could be the order by clause or partition by clause.
|
You've not used it at all.
Try adding this to your query and see if inspiration strikes:
,row_number() over (partition by depno order by empid) rnum
|
|
|
Re: Data display from table [message #410182 is a reply to message #410159] |
Thu, 25 June 2009 10:01   |
souvik_roy
Messages: 16 Registered: June 2008
|
Junior Member |
|
|
Thanks Michel,
I have been able to show the name position wise in a dept.
The fianl SQL query I have written is:
select nvl(nvl(dep_10,dep_20),dep_30) ak,rn
from
(SELECT decode(depno,10,empname) dep_10,
decode(depno,20,empname) dep_20,decode(depno,30,empname) dep_30,row_number() over(partition by depno order by empid) rn
from emp1) e2 order by rn
which gives me the output as:
AK RN
A 1
C 1
D 1
M 2
J 2
K 2
F 3
E 3
G 4
But I have to show the empname dep wise wrt the rownum i.e. rn would be of 3 rows (1,2 and 3) and corresponding empname for each dep in 3 columns.
Do you have any suggestion.
|
|
|
|
|
|
|
Re: Data display from table [message #410310 is a reply to message #410305] |
Fri, 26 June 2009 01:41   |
souvik_roy
Messages: 16 Registered: June 2008
|
Junior Member |
|
|
Thanks a lot Michel.
It really works for me.
The final query is:
select max(dep_10) dep_10,max(dep_20) dep_20,max(dep_30) dep_30 from (SELECT decode(depno,10,empname) dep_10,
decode(depno,20,empname) dep_20,decode(depno,30,empname) dep_30,row_number() over(partition by depno order by empid) rn
from emp1) group by rn
The final output is:
DEP_10 DEP_20 DEP_30
A C D
K M J
E F
G
|
|
|
|
Goto Forum:
Current Time: Sun Feb 16 00:12:08 CST 2025
|