SQL Query to display a repeated row once in the output-CodePudding

I have written this query:

select 
    person_number ,
    elements,
    batchid,
    co_code
from 
    per_all_people_f papf,
    elements_tab elements,
    batch_tab batch,
    company_det co,
where 
    papf.person_id = company_det.person_id
    and elements.element_id = company_det.element_id
    and batch.batchid = co.batchid

union all

select 
    person_number ,
    elements.absence_name elements,
    batchid,
    co_code
from 
    per_all_people_f papf,
    absence_tab elements,
    batch_tab batch,
    company_det co,
where 
    papf.person_id = company_det.person_id
    and elements.absence_id =company_det.absence_id
    and batch.batchid = co.batchid

This returns an output like:

person_number       elements         batchid        co_code
1100928             Benefits        20209181        XYZ
1100928             Benefits        20208361        ARE
1100928             Benefits        92725378        FREELISTS

Can i tweak the query so that the output looks like -

person_number       elements         batchid        co_code
1100928             Benefits        20209181        XYZ
1100928                             20208361        ARE
1100928                             92725378        FREELISTS

i.e. if for the person_number 1100928 the elements Benefits is repeated then it should come in the output only once and not thrice

CodePudding user response：

If you're using SQL*Plus, then break helps.

For example (as I don't have your tables), this is what you have now:

SQL> select d.dname, e.ename, e.job, e.sal
  2  from emp e join dept d on d.deptno = e.deptno
  3  order by d.dname, e.ename;

DNAME          ENAME      JOB              SAL
-------------- ---------- --------- ----------
ACCOUNTING     CLARK      MANAGER         2450
ACCOUNTING     KING       PRESIDENT       5000
ACCOUNTING     MILLER     CLERK           1300
RESEARCH       ADAMS      CLERK           1100
RESEARCH       FORD       ANALYST         3000
RESEARCH       JONES      MANAGER         2975
RESEARCH       SCOTT      ANALYST         3000
RESEARCH       SMITH      CLERK            800
SALES          ALLEN      SALESMAN        1600
SALES          BLAKE      MANAGER         2850
SALES          JAMES      CLERK            950
SALES          MARTIN     SALESMAN        1250
SALES          TURNER     SALESMAN        1500
SALES          WARD       SALESMAN        1250

14 rows selected.

With a BREAK:

SQL> break on dname
SQL> /

DNAME          ENAME      JOB              SAL
-------------- ---------- --------- ----------
ACCOUNTING     CLARK      MANAGER         2450
               KING       PRESIDENT       5000
               MILLER     CLERK           1300
RESEARCH       ADAMS      CLERK           1100
               FORD       ANALYST         3000
               JONES      MANAGER         2975
               SCOTT      ANALYST         3000
               SMITH      CLERK            800
SALES          ALLEN      SALESMAN        1600
               BLAKE      MANAGER         2850
               JAMES      CLERK            950
               MARTIN     SALESMAN        1250
               TURNER     SALESMAN        1500
               WARD       SALESMAN        1250

14 rows selected.

SQL>

Generally speaking, that kind of formatting is a matter of reporting tool you use, not SQL itself.

P.S. Jeff Smith said that break works in SQL Developer as well (How to Format Your Script Output in SQL Developer ... By Default), but that text is related to version 4.1. I can't reproduce it on newest 21.2 version.

CodePudding user response：

Wrap it a sub-query, and check with LAG if the previous elements hasn't changed.

select 
  person_number
, case 
  when elements = lag(elements) over (partition by person_number order by batch_id)
  then '' 
  else elements 
  end as elements
, batchid
, co_code
from
(
    select 
     papf.person_number, 
     elem.elements, 
     batch.batchid, 
     comp.co_code
    from company_det as comp
    join per_all_people_f as papf 
      on papf.person_id = comp.person_id
    join elements_tab as elem
      on elem.element_id = comp.element_id
    join batch_tab as batch
      on batch.batchid = comp.batchid 

    union all

    select 
     papf.person_number,
     absc.absence_name,
     batch.batchid,
     comp.co_code
    from company_det as comp
    join per_all_people_f as papf
      on papf.person_id = comp.person_id
    join absence_tab as absc
      on absc.absence_id = comp.absence_id
    join batch_tab as batch
      on batch.batchid = comp.batchid
) q
order by person_number, batch_id