Suppressing a repeated column value in PostgreSQL?

Question

For example if we have the values:

username             Category
-------------------- ----------
Brian                cs
Tom                  cs
Joe                  cs
Allen                cs
Bill                 ts
Steven               ts
Fred                 ts
Ted                  ts

I Need an Output as

username             Category
-------------------- ----------
Brian                cs
Tom                  
Joe                  
Allen                
Bill                 ts
Steven               
Fred                 
Ted

I have Tried

1) T-SQL How to select rows without duplicate values from one column?

2) http://www.rajapet.com/2010/04/suppressing-repeated-column-value-in.html

3) How to select records without duplicate on just one field in SQL?

Nothing Work well ( What i Expected)

Is there any other way ?

The canonical way to do this is to do all visual formatting in your report writer, not in SQL. I think you'll find that most report writers will have something like "suppress repeating values". — Mike Sherrill 'Cat Recall', Jan 24 '15 at 23:08

score 6 · Answer 1 · 2015-01-23T11:25:11.763

6

You can use a window function for this:

select username, 
       case when rn = 1 then cat else '' end as category
from (
   select username, 
          category as cat, 
          row_number() over (partition by category order by username) as rn
   from the_table
) t
order by cat, username;

SQLFiddle: http://sqlfiddle.com/#!15/423ed/4

edited Jan 23 '15 at 11:25

answered Jan 23 '15 at 11:19

mlinth · Answer 2 · 2015-01-23T11:34:24.990

Here's an alternative way, using a subquery. Note that both answers will give the category value to the first username alphabetically i.e. Allen will get cs, and Bill ts.

select a.username,b.category from
foo a
left join
(select category,min(username) as username from foo group by category) b
on b.username = a.username
order by a.category,a.username

[EDIT] If you want to assign to the first instance in your table (i.e. return what you put in your question), you can do something like this:

    with foobar as 
        (select username,category, row_number() over() as rownum from foo )

   select a.username,coalesce(b.category,'') as category from
    foobar a
    left join
    (select category,min(rownum) as rownum from foobar group by category) b
    on b.rownum = a.rownum
    order by a.rownum,a.username

You might want a COALESCE or something similar to make NULLs into empty. — Mihai, Jan 23 '15 at 11:28

Suppressing a repeated column value in PostgreSQL?

2 Answers2