Suppressing repeating values in SQL

Sometimes you my find a need to suppress repeating values in SQL. On 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.

Comments

[freelance writing]

this report is for what kind?