How to display if one column data is duplicate and I want to display in next row with other colums is null data. Need to prepare in OAS or query?

Question

My current result:

Column A	Column B	Column B
01	AB	col_amount
01	AB	col_amount
01	AB	col_amount
02	OA	col_amount
03	OE	col_amount
03	OE	col_amount
04	DB	col_amount

I want to this output:

Column A	Column B	Column B
01	AB	col_amount
		col_amount
		col_amount
02	OA	col_amount
03	OE	col_amount
		col_amount
04	DB	col_amount

Answer 1

If you do not have a client application that supports the BREAK command (which is not an SQL command but is a command for the SQL*Plus client or other similar clients) then you can use the ROW_NUMBER analytic function and CASE expressions:

SELECT CASE a_rn WHEN 1 THEN column_a END AS column_a,
       CASE b_rn WHEN 1 THEN column_b END AS column_b,
       column_c
FROM   (
  SELECT column_a,
         column_b,
         column_c,
         ROW_NUMBER() OVER (PARTITION BY column_a ORDER BY column_b, column_c)
           AS a_rn,
         ROW_NUMBER() OVER (PARTITION BY column_a, column_b ORDER BY column_c)
           AS b_rn
  FROM   table_name
)

Which, for the sample data:

CREATE TABLE table_name (Column_A, Column_B, Column_C) AS
SELECT '01', 'AB', 'col_amount' FROM DUAL UNION ALL
SELECT '01', 'AB', 'col_amount' FROM DUAL UNION ALL
SELECT '01', 'AB', 'col_amount' FROM DUAL UNION ALL
SELECT '02', 'OA', 'col_amount' FROM DUAL UNION ALL
SELECT '03', 'OE', 'col_amount' FROM DUAL UNION ALL
SELECT '03', 'OE', 'col_amount' FROM DUAL UNION ALL
SELECT '04', 'DB', 'col_amount' FROM DUAL;

Outputs:

COLUMN_A	COLUMN_B	COLUMN_C
01	AB	col_amount
null	null	col_amount
null	null	col_amount
02	OA	col_amount
03	OE	col_amount
null	null	col_amount
04	DB	col_amount

Note: In Oracle, NULL and an empty string are identical.

fiddle

Answer 2

A simple option - if you use SQL*Plus - is break command.

Before:

SQL> select deptno, job, ename from emp order by deptno, job, ename;

    DEPTNO JOB       ENAME
---------- --------- ----------
        10 CLERK     MILLER
        10 MANAGER   CLARK
        10 PRESIDENT KING
        20 ANALYST   FORD
        20 ANALYST   SCOTT
        20 CLERK     ADAMS
        20 CLERK     SMITH
        20 MANAGER   JONES
        30 CLERK     JAMES
        30 MANAGER   BLAKE
        30 SALESMAN  ALLEN
        30 SALESMAN  MARTIN
        30 SALESMAN  TURNER
        30 SALESMAN  WARD

14 rows selected.

After:

SQL> break on deptno on job
SQL> select deptno, job, ename from emp order by deptno, job, ename;

    DEPTNO JOB       ENAME
---------- --------- ----------
        10 CLERK     MILLER
           MANAGER   CLARK
           PRESIDENT KING
        20 ANALYST   FORD
                     SCOTT
           CLERK     ADAMS
                     SMITH
           MANAGER   JONES
        30 CLERK     JAMES
           MANAGER   BLAKE
           SALESMAN  ALLEN
                     MARTIN
                     TURNER
                     WARD

14 rows selected.

SQL>

---

If you don't use SQL*Plus, you can simulate break command with lag analytic function, e.g.

SQL> select
  2  case when deptno = lag(deptno) over (order by deptno, job, ename) then null
  3       else deptno
  4  end deptno,
  5  case when job = lag(job) over (order by deptno, job, ename) then null
  6       else job
  7  end job,
  8  ename
  9  from emp;

    DEPTNO JOB       ENAME
---------- --------- ----------
        10 CLERK     MILLER
           MANAGER   CLARK
           PRESIDENT KING
        20 ANALYST   FORD
                     SCOTT
           CLERK     ADAMS
                     SMITH
           MANAGER   JONES
        30 CLERK     JAMES
           MANAGER   BLAKE
           SALESMAN  ALLEN
                     MARTIN
                     TURNER
                     WARD

14 rows selected.

SQL>