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 Go to next message
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 #409831 is a reply to message #409829] Wed, 24 June 2009 03:51 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Use ROW_NUMBER function to number the values in each dept and group by this number.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Post a working Test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Re: Data display from table [message #409865 is a reply to message #409831] Wed, 24 June 2009 04:55 Go to previous messageGo to next message
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 #409881 is a reply to message #409865] Wed, 24 June 2009 06:14 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
The following is the sql I have used:

And this is what I recommend:
Quote:
Use ROW_NUMBER function to number the values in each dept and group by this number.

Regards
Michel

[Updated on: Wed, 24 June 2009 06:15]

Report message to a moderator

Re: Data display from table [message #409898 is a reply to message #409865] Wed, 24 June 2009 07:00 Go to previous messageGo to next message
ds285269
Messages: 10
Registered: June 2009
Location: Mumbai
Junior Member
I think it's not working....
Re: Data display from table [message #409908 is a reply to message #409898] Wed, 24 June 2009 07:15 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
ds285269 wrote on Wed, 24 June 2009 14:00
I think it's not working....

You think wrong.

Regards
Michel

Re: Data display from table [message #409936 is a reply to message #409908] Wed, 24 June 2009 09:18 Go to previous messageGo to next message
souvik_roy
Messages: 16
Registered: June 2008
Junior Member
Michel,

Could you help me build the exact SQL query..

Regards,
Souvik
Re: Data display from table [message #409970 is a reply to message #409936] Wed, 24 June 2009 10:58 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Maybe you could post what you trued with the function I mentioned.

Regards
Michel
Re: Data display from table [message #410153 is a reply to message #409970] Thu, 25 June 2009 08:37 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #410185 is a reply to message #410182] Thu, 25 June 2009 10:24 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
What does the inner query give?

Regards
Michel
Re: Data display from table [message #410286 is a reply to message #410182] Fri, 26 June 2009 00:19 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
souvik_roy wrote on Thu, 25 June 2009 17:01
Thanks Michel

You should really work on your reading skills.. first you fail to see the difference between rownum and row_number several times, now you fail to see the name of the poster you are replying to.
For a coder, it is important that (from time to time Smile ) you know how to be precise..
Re: Data display from table [message #410303 is a reply to message #410286] Fri, 26 June 2009 01:11 Go to previous messageGo to next message
souvik_roy
Messages: 16
Registered: June 2008
Junior Member
I really apologize Frank for this.

Michel,

The output of the inner query is:
DEP_10	DEP_20	DEP_30	RN

A			1
K			2
E			3
G			4
	C		1
	M		2
		D	1
		J	2
		F	3
Re: Data display from table [message #410305 is a reply to message #410303] Fri, 26 June 2009 01:16 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Good, now group the values by the last column applying an aggregate function (for instance MAX) to other ones.

Regards
Michel

Re: Data display from table [message #410310 is a reply to message #410305] Fri, 26 June 2009 01:41 Go to previous messageGo to next message
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		
Re: Data display from table [message #410350 is a reply to message #410310] Fri, 26 June 2009 08:26 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
That's ok <sniff> - I didn't really want any recognition.....
Previous Topic: using 'with' analytical function
Next Topic: Wrapping multiple cursors resultset into one? (merged 2)
Goto Forum:
  


Current Time: Sun Feb 16 00:12:08 CST 2025