Oracle SQL hiding duplicate values

Question

I have a query with these results:

A | 1
A | 2
B | 1
B | 2
B | 3

How do I get the results to be like this:

A | 1
  | 2  
B | 1   
  | 2   
  | 3

Answer 1

Here is one way:

SELECT CASE WHEN rn = 1 THEN c1 ELSE NULL END || ' | ' || c2
  FROM (SELECT c1, c2, ROW_NUMBER() OVER (PARTITION BY c1 ORDER BY c2) rn
          FROM your_table);

Answer 2

You can use BREAK ON if you are using sqlplus:

SQL> desc tab1;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL                                                VARCHAR2(1)
 COL2                                               NUMBER

SQL> select * from tab1;

C       COL2
- ----------
A          1
A          2
B          1
B          2
B          3

SQL> break on col;
SQL> select * from tab1;

C       COL2
- ----------
A          1
           2
B          1
           2
           3

SQL>

More details here.

Answer 3

I vaugely remember thereis a way to get this format in SQL PLus.. Another possible way is as given below:

SELECT a.COLUMN_1,
             CASE 
                WHEN a.rnk = 1 THEN a.COLUMN_2 
                ELSE NULL
            END AS COLUMN_2 
  FROM (
                SELECT a.*,
                    RANK() OVER(PARTITION BY COLUMN_1 ORDER BY COLUMN2) rnk
                    FROM <YOUR_TABLE> a
                ) a

Answer 4

This presentation requirement is best served in the application you're using to display the results, rather than in the raw SQL. You could meet your requirements with a cursor, but it's not a very elegant solution.

Answer 5

This is more of a formatting issue, best solved by whatever you are using to display the output. There's nothing wrong with the query result, as a query result.

Answer 6

in oracle, check out the LEAD and LAG functions.

you can look at the previous row, and if it is the same as the current row, change the value to NULL.