Hide data for all but first row on left join column

Let’s say I have this query for a view:

select a.name as Author,b.name as Book
from a
left join b on a.id = b.a_id

and it produces this output:

Brandon Sanderson Mistborn
Brandon Sanderson Way of Kings
Brandon Sanderson Steelheart
Patrick Rothfuss  The Wise Man's Fear
Patrick Rothfuss  The Name of the Wind

All good so far, but I’d like to hide the duplicates in the left row:

Brandon Sanderson Mistborn
                  Way of Kings
                  Steelheart
Patrick Rothfuss  The Wise Man's Fear
                  The Name of the Wind

That makes is easier to read IMO, and it’s the formatting I want for this view.

How can I achieve this?

Answer

Well, this can be implemented by a window function, if the database what you use now supports this feature such as PostgreSQL or MySQL of high version(8.0+), you can write the SQL as below:

select * from books;
      author       |         book         
-------------------+----------------------
 Brandon Sanderson | Mistborn
 Brandon Sanderson | Way of Kings
 Brandon Sanderson | Steelheart
 Patrick Rothfuss  | The Wise Man’s Fear
 Patrick Rothfuss  | The Name of the Wind
(5 rows)

select
    case when row_number() over(partition by author order by book) > 1 then null else author end as author,
    book
from
    books;
      author       |         book         
-------------------+----------------------
 Brandon Sanderson | Mistborn
                   | Steelheart
                   | Way of Kings
 Patrick Rothfuss  | The Name of the Wind
                   | The Wise Man's Fear
(5 rows)

with tmp as (
select
    author,
    book,
    row_number() over(partition by author order by book) as sort
from
    books
)
select
    case when sort>1 then null else author end as th_author,
    book
from
    tmp
order by
    author,sort;
     th_author     |         book         
-------------------+----------------------
 Brandon Sanderson | Mistborn
                   | Steelheart
                   | Way of Kings
 Patrick Rothfuss  | The Name of the Wind
                   | The Wise Man‘s Fear

Or the database does not support window function, you can write as below:

with named_book as (
select
    author,
    min(book) as book
from
    books
group by
    author
)
select
    b.author,
    a.book
from
    books a
left join
    named_book b on a.book = b.book
order by
    a.author,a.book;
      author       |         book         
-------------------+----------------------
 Brandon Sanderson | Mistborn
                   | Steelheart
                   | Way of Kings
 Patrick Rothfuss  | The Name of the Wind
                   | The Wise Man's Fear
(5 rows)