How to eliminate repeating identical values with MSSQL

  • Repeating values must be included in the reports to make it accurate, but they make it difficult to read views/reports.
  • Often we don't want to print the repeating values on reports and replace them with blanks, to clarify the views/reports.

Applies to:

  • Microsoft SQL Server 2000
  • Microsoft SQL Server 2005
  • Microsoft SQL Server 2008
  • This is much better done by doing it on the front-end, the report writer.
  • The report writer is much more efficient at that, and most of the time the rendering portion is done on the client PC and not on the server, reducing the server load.

Data used

  1. USE sql911;
  2. go
  3. SELECT deptno, projno, projname
  4. FROM projects
  5. ORDER BY deptno, projno;
  6. go
deptno projno projname
------ ------ ------------------------
B01    PL2100 WELD LINE PLANNING
C01    IF1000 QUERY SERVICES
C01    IF2000 USER EDUCATION
D01    AD3100 ADMIN SERVICES
D01    MA2100 WELDLINE AUTOMATION
D11    MA2110 W L PROGRAMMING
D11    MA2111 W L PROGRAM DESIGN
D11    MA2112 W L ROBOT DESIGN
D11    MA2113 W L PRODCONT PROGS
D21    AD3110 GENERAL ADMINSYSTEMS
D21    AD3111 PAYROLL PROGRAMMING
D21    AD3112 PERSONNEL PROGRAMMING
D21    AD3113 ACCOUNT PROGRAMMING
E01    OP1000 OPERATION SUPPORT
E01    OP2000 GEN SYSTEM SSERVICES
E11    OP1010 OPERATION
E21    OP2010 SYSTEMS SUPPORT
E21    OP2011 SCP SYSTEMS SUPPORT
E21    OP2012 APPLICATIONS SUPPORT
E21    OP2013 DB/DCSUPPORT

(20 row(s) affected)

Eliminating the repeating values

  1. SELECT (CASE WHEN projno = first_line
  2.             THEN deptno
  3.             ELSE ' '
  4.        END) deptno,
  5.        projno,
  6.        projname
  7. FROM (
  8.       SELECT deptno,
  9.       MIN(projno) OVER (partition BY deptno) first_line,
  10.       projno, projname
  11.       FROM projects) abc
  12. go
deptno projno projname
------ ------ ------------------------
B01    PL2100 WELD LINE PLANNING
C01    IF1000 QUERY SERVICES
       IF2000 USER EDUCATION
       MA2100 WELDLINE AUTOMATION
D01    AD3100 ADMIN SERVICES
D11    MA2110 W L PROGRAMMING
       MA2111 W L PROGRAM DESIGN
       MA2112 W L ROBOT DESIGN
       MA2113 W L PRODCONT PROGS
D21    AD3110 GENERAL ADMINSYSTEMS
       AD3111 PAYROLL PROGRAMMING
       AD3112 PERSONNEL PROGRAMMING
       AD3113 ACCOUNT PROGRAMMING
       OP2000 GEN SYSTEM SSERVICES
E01    OP1000 OPERATION SUPPORT
E11    OP1010 OPERATION
E21    OP2010 SYSTEMS SUPPORT
       OP2011 SCP SYSTEMS SUPPORT
       OP2012 APPLICATIONS SUPPORT
       OP2013 DB/DCSUPPORT

(20 row(s) affected)
  • The secret sauce is the windowing function on line 9: min(projno) over (partition by deptno) first_line.
  • The min() over (partition finds the first/smallest value of the project for the department. This is the one that gets printed with the case statement. The other ones just become either blanks or nulls.
  • The case statement on line 1,2,3, and 4: case when projno = first_line then deptno else ' ' end control the display of of the department after the min(projno) over (partition by deptno) has identified the first occurrence of the department.