Oracle SQL hiding duplicate values

I have a query with these re开发者_运维知识库sults:

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

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);

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.

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

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.

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.

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.