Suppressing repeating values in SQL

11 10 2011

Sometimes you my find a need to suppress repeating values in SQL. One case is when your reporting tool does not have such a feture or you just cannot find it fast enough. That happened to me with a report that was to be converted to APEX from Oracle Reports the other week.

I could not find an option in APEX to suppress repeating values, and I did not want to make them all control breaks as that would chop up the report too much.

Let’s begin with a real simple SQL that shows the departments different employees work in.

select a.dname
,b.ename
from dept a
inner join emp b
on a.deptno = b.deptno
order by 1,2

This of course gives a list like this:

ACCOUNTING CLARK
ACCOUNTING KING
ACCOUNTING MILLER
RESEARCH ADAMS
RESEARCH FORD
RESEARCH JONES
RESEARCH SCOTT
RESEARCH SMITH
SALES ALLEN
SALES BLAKE
SALES JAMES
SALES MARTIN
SALES TURNER
SALES WARD

Suppose I want to remove the department name when it is the same as on the previous row. SQL does not give a way to just mention a keyword to have it done and APEX does not allow for it as far as I can tell.

The first step is to add a rownumber to the result table for the above SQL.
select c.*
,rownum rn
from (select a.dname
,b.ename
from dept a
inner join emp b
on a.deptno = b.deptno
order by 1,2) c

Now each row has an number starting from 1 and ending with 14. We wrap this in a prefactor construct (i.e. wrap it in a with so the select can use the data).

with rpt as(select c.*
,rownum rn
from (select a.dname
,b.ename
from dept a
inner join emp b
on a.deptno = b.deptno
order by 1,2) c)
select case
when a.dname = b.dname then ' '
else a.dname
end dname
,a.ename
from rpt a left outer join rpt b
on a.rn = b.rn + 1
order by a.rn;

Here we have the select creating the list with the rownumber in a with that the select that follows can reference. We have now also added a selfjoin so we join each row with its predecessor in rownumber order.

This allows us to show data from the prefactored SQl using a to display data and b to check data on the previous row.

In this case we check the department and when it is the same as it was on the previous row we display a single space. if it is not the same, we display the name of the department. Thus, the case performs the supressing of repÄeating department names.

The result of this SQL is the following report.

ACCOUNTING CLARK
KING
MILLER
RESEARCH ADAMS
FORD
JONES
SCOTT
SMITH
SALES ALLEN
BLAKE
JAMES
MARTIN
TURNER
WARD

That is it. Suppressing isn’t too hard to do, but it requires a little bit of setup in the SQL.

About these ads

Actions

Information

6 responses

11 06 2012
Gabriel

Aren’t charts to make thngis clearer by showing data in something the eye/brain can recognize in an instant? So .the above is a neat trick, but only for the purpose of showing off. What’s the use? Please enlighten me, because I’m totally missing the point why you would waste your time and talent on this.

22 10 2012
mathiasmag

I fail to understand why suppressing repeating data would be showing off or a waste of my time and talent? To me, that is a very common request in reporting. When creating pure text reports or a reporting engine with no native support for it, being able to do it in SQL is a very useful tool to have in the toolbox.

5 10 2012
FarMan

Dude, you just saved my life. Thanks so much!

22 10 2012
mathiasmag

This is what makes blogs so much fun. What to one person is a way to show off is to someone else critical skills to get the job done.

1 11 2012
Ronald Hollak

Or U could use analytic function:
select
case d.name when lag(d.name) over (order by d.name) then ‘ ‘ else d.name end dname
, e.last_name
from emp e join dept d on (e.deptno = d.deptno)
order by d.name, e.last_name;

1 11 2012
mathiasmag

I don’t know this U person. But I myself agree that you could use analytical SQL. Some places does not allow the use, but I think it is a great tool.

The more ways we know to achieve a result the better equipped we are to deal with different kinds of problems.

Thank you for contributing an alternative way to get the same end result. Using analytical SQL sure allows for elegant solutions to some problems.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




%d bloggers like this: