Sybase, show all rows but don't display column data when duplicate

Question

Product: Sybase ASE 11/12/15/16

I am looking to update a Stored Procedure that gets called by different applications, so changing the application(s) isn't an option. What is needed is best explained in examples:

Current results:

type | breed            | name
------------------------------------
dog  | german shepherd  | Bernie
dog  | german shepherd  | James
dog  | husky            | Laura
cat  | british blue     | Mr Fluffles
cat  | other            | Laserchild
cat  | british blue     | Sleepy head
fish | goldfish         | Goldie

What I need is for the First column's data to be cleared on duplicates. For example, the above data should look like:

type | breed            | name
------------------------------------
dog  | german shepherd  | Bernie
     | german shepherd  | James
     | husky            | Laura
cat  | british blue     | Mr Fluffles
     | other            | Laserchild
     | british blue     | Sleepy head
fish | goldfish         | Goldie

I know I can do a cursor, but there are around 10,000 records and that doesn't seem proficient. Looking for a select command, don't want to change the data in the database.

Are you wanting to change the data in a table or the output of a select statement? — JSR, Mar 22 '17 at 16:10
This does not work in SQL, really. You would have to do that in the front end. SQL tables are tabular, which means that columns in every row are only empty when they contain NULL values. This is not the case for you. — marcothesane, Mar 22 '17 at 16:14

score 0 · Answer 1 · edited May 23 '17 at 11:54

After mulling over this, I found a solution that would work and not use a cursor.

select Type,breed,name
    into #DontDisplay
    from #MyDataList as a1
    group by breed
    Having breed= (select max(name)
                            from #MyDataList a2
                                where a1.breed= a2.breed)
    order by breed, name

    select n.Type,d.Breed,d.Name 
    from #MyDataList as d
    left join #DontDisplay as n
    on d.Breed= n.Breed and d.Name= n.Name
    order by Breed

Works great and the solution was based on another solution Sybase SQL Select Distinct Based on Multiple Columns with an ID

Sybase, show all rows but don't display column data when duplicate

1 Answers1