Home » SQL & PL/SQL » SQL & PL/SQL » How to hide repeated details using SQL Query? (Oracle SQL)
How to hide repeated details using SQL Query? [message #339651] Fri, 08 August 2008 01:20 Go to next message
hioraapps
Messages: 4
Registered: August 2008
Location: Hyderabad
Junior Member

How to hide repeated details using SQL Query?
For Ex:
------------------------+
DEPTNO| ENAME | JOB |
------|-------| --------|
10 | JAMES | MANAGER |
10 | BLAKE | CLERK |
10 | FORD | SALESMAN|
20 | SCOTT | MANAGER |
20 | ADAMS | CLERK |
20 | KING | SALESMAN|
------------------------+
How we can display the above details in the following way?

------------------------+
DEPTNO| ENAME | JOB |
------|-------| --------|
10 | JAMES | MANAGER |
| BLAKE | CLERK |
| FORD | SALESMAN|
20 | SCOTT | MANAGER |
| ADAMS | CLERK |
| KING | SALESMAN|
------------------------+

Can any one help me ?

Thanks
Sree

[Updated on: Fri, 08 August 2008 01:26]

Report message to a moderator

Re: How to hide repeated details using SQL Query? [message #339653 is a reply to message #339651] Fri, 08 August 2008 01:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
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) and align the columns in result.
Use the "Preview Message" button to verify.

Always post your Oracle version (4 decimals).

Search before posting this has been asked and answered many times. Keyword: "break"

Regards
Michel
Re: How to hide repeated details using SQL Query? [message #339665 is a reply to message #339653] Fri, 08 August 2008 01:55 Go to previous messageGo to next message
hioraapps
Messages: 4
Registered: August 2008
Location: Hyderabad
Junior Member

Hi Michel,

Thanks for your quick response and sorry about the format.
I don't want to use any sql*plus commands here. Actually I need to display the details in my Page. Is it possible to show null if data get's repeated ?

Thanks
Sree
Re: How to hide repeated details using SQL Query? [message #339666 is a reply to message #339665] Fri, 08 August 2008 01:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes search for the keyword I said.

Regards
Michel
Re: How to hide repeated details using SQL Query? [message #339686 is a reply to message #339666] Fri, 08 August 2008 02:24 Go to previous messageGo to next message
hioraapps
Messages: 4
Registered: August 2008
Location: Hyderabad
Junior Member

Hi Mike,

I am aware about using Break SQL* Plus command, but I need the results with out using the break command like "Break on deptno".
If I use the select statment in a cursor I shoud get null value if column value get's repeated.

Thanks
Sreehari

Re: How to hide repeated details using SQL Query? [message #339690 is a reply to message #339686] Fri, 08 August 2008 02:27 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Generally, display formatting like this is done in the client, rather than in SQL (hence the reason that SQL*Plus is used).

If you search for BREAK, you will find other solutions listed in those threads.

The easiest is probably to order your resultset, and then use the LAG analytic function to conditionally modify the columns that you want to set to blank.
Re: How to hide repeated details using SQL Query? [message #339700 is a reply to message #339686] Fri, 08 August 2008 02:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Sreehari, just search as I said and you will have your answer.
Just search, do this effort don't answer BEFORE trying what we say you.
If I repeat the answer there is a reason.

Regards
Michel
Re: How to hide repeated details using SQL Query? [message #339710 is a reply to message #339700] Fri, 08 August 2008 03:14 Go to previous messageGo to next message
hioraapps
Messages: 4
Registered: August 2008
Location: Hyderabad
Junior Member

Hi Michel,

Sorry to trouble you.
Now I got it. Thanks a lot.

Query :
select decode (deptno,LAG(deptno, 1, 0) OVER (ORDER BY deptno),null,deptno) deptno ,empno,ename,sal
from emp

Thanks
Sree
Re: How to hide repeated details using SQL Query? [message #339713 is a reply to message #339710] Fri, 08 August 2008 03:23 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
I don't whether this is sheer coincidence. Check this link.

http://forums.oracle.com/forums/thread.jspa?threadID=692346&tstart=0

Regards

Raj
Previous Topic: Analytical function
Next Topic: How to update the rows?
Goto Forum:
  


Current Time: Sat Dec 03 18:18:29 CST 2016

Total time taken to generate the page: 0.08795 seconds