sqloracle-databaseoas

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?


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

Solution

  • 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