CODEWITHSUNDEEP
sqloracle-database
Michael
Michael

Reputation: 21

Oracle SQL hiding duplicate values

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

Upvotes: 2

Views: 6136

Answers (6)

Lazer
Lazer

Reputation: 94890

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.

Upvotes: 3

DCookie
DCookie

Reputation: 43533

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

Upvotes: 4

Randy
Randy

Reputation: 16677

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.

Upvotes: 1

Chandu
Chandu

Reputation: 82933

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

Upvotes: 2

FrustratedWithFormsDesigner
FrustratedWithFormsDesigner

Reputation: 27496

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.

Upvotes: 1

Steve Mayne
Steve Mayne

Reputation: 22828

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.

Upvotes: 1

Related Questions