Geek Answers Handbook

SQL - Recursive conditions in a SELECT clause

I'm not the best in SQL, so sorry if I ask stupid questions :)

Let there be 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 always unique.

And I'm trying to get these results:

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

In principle, the logic:

  • If name empty , then display_name = 'nickname'
  • If name unique , then display_name = 'name'
  • If name not_unique , then display_name = 'name (nickname)'

Can I achieve this with just one SQL query? If so - how? If not, what are the alternatives?

I am currently doing this with my programming language, but I need to send another SQL query for each row of the result to check if there are other records with the same name that is suitable for filtered results, but is too greedy to get the whole table (4,000 rows and growth).

+7
sql sql-server
source share
2 answers

You can use the version of the window function COUNT() to determine if name unique for a given string 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
+7
source share

You can try this -

 SELECT * ,display_name = CASE WHEN (ROW_NUMBER() OVER (PARTITION BY name ORDER BY id) = 1) THEN name WHEN ISNULL(name, '') = '' THEN nickname ELSE name + ' (' + nickname + ')' END FROM MyTable
+2
source share

More articles:


All Articles