SQL - Recursive conditions in SELECT clause

Question

I am not the best in SQL so sorry if I'm asking stupid questions :)

Let's have a table like this:

|id|  name  |  nickname  |
|==|========|============|
| 1|      Jo|      Banana|
| 2|Margaret|The Princess|
| 3|   Perry|  The Monkey|
| 4|Margaret|   The Queen|
| 5|        |     The Rat|
| 6|        |     The Cat|

where nickname is always unique.

And I'm trying to get results like this:

|id|  name  |  nickname  |     display_name      |
|==|========|============|=======================|
| 1|      Jo|      Banana|                     Jo|
| 2|Margaret|The Princess|Margaret (The Princess)
| 3|   Perry|  The Monkey|                  Perry|
| 4|Margaret|   The Queen|   Margaret (The Queen)|
| 5|        |     The Rat|                The Rat|
| 6|        |     The Cat|                The Cat|

Basically the logic is:

1. If name is empty then display_name = 'nickname'
2. If name is unique then display_name = 'name'
3. If name is not_unique then display_name = 'name (nickname)'

Can I achieve it with one SQL query only? If so - how? If not - what are the alternatives?

Currently I do it with my programing language but I have to send for each row of the result another SQL query to check if there are other records with the same name which is OK for filtered results but is too greedy when retrieving the whole table (4 000 rows and growing).

Answer 1

You can use the window function version of COUNT() to determine whether the name for a given row is unique without performing a subquery. For example:

SELECT
  id, name, nickname,
  CASE
    WHEN ISNULL(name, '') = '' THEN nickname
    WHEN (COUNT(*) OVER (PARTITION BY name)) = 1 THEN name
    ELSE name + ' (' + nickname + ')'
  END AS display_name
FROM my_table