SQL

Suppressing repeating values in SQL

Sometimes you my find a need to suppress repeating values in SQL. One case is when your reporting tool does not have such a feature 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:

ACCOUNTINGCLARK
ACCOUNTINGKING
ACCOUNTINGMILLER
RESEARCHADAMS
RESEARCHFORD
RESEARCHJONES
RESEARCHSCOTT
RESEARCHSMITH
SALESALLEN
SALESBLAKE
SALESJAMES
SALESMARTIN
SALESTURNER
SALESWARD
Output from above SQL

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 row number to the result table for the above SQL.

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

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 row number in a with that the select that follows can reference. We have now also added a self join so we join each row with its predecessor in row number 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 repeating department names.

The result of this SQL is the following report.

ACCOUNTINGCLARK
 KING
 MILLER
RESEARCHADAMS
 FORD
 JONES
 SCOTT
 SMITH
SALESALLEN
 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.

13 Comments

  1. 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.

    • 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.

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

  3. 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;

    • 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.

  4. I got this to work for two columns in Apex, but I actually have 3 columns and the 3rd column repeats. For example, in the above scenario, to display the dept head’s name one time for each, not repeating for each dept. Would that need another outer join?

  5. rajasekark

    please tell what is (rownum, rpt)
    i got error in step
    please tell me how to do it

  6. i am not able to display dept name from emp table

    my table

    table : dept
    deptno dname
    1 ACCOUNTING
    1 ACCOUNTING
    1 ACCOUNTING

    2 RESEARCH
    2 RESEARCH
    2 RESEARCH
    2 RESEARCH
    2 RESEARCH

    3 SALES
    3 SALES
    3 SALES
    3 SALES
    3 SALES
    3 SALES

    table : emp
    deptno dname ename
    1 ACCOUNTING CLARK
    1 ACCOUNTING KING
    1 ACCOUNTING MILLER

    2 RESEARCH ADAMS
    2 RESEARCH FORD
    2 RESEARCH JONES
    2 RESEARCH SCOTT
    2 RESEARCH SMITH

    3 SALES ALLEN
    3 SALES BLAKE
    3 SALES JAMES
    3 SALES MARTIN
    3 SALES TURNER
    3 SALES WARD

    • I do not understand your problem. You have to tablkes with columns, what SQL do you use and what issue do you get in relation to this post?

  7. Bob Piskac

    Thank you, I need to do this too!!!!!!!!

  8. Pingback: Is blogging another word for bragging? – Oracle Database Development

Leave a Comment

Your email address will not be published. Required fields are marked *

*