Q:

CASE in a SELECT statement and multiple expression at THEN

CASE  syntax.
SELECT 
   ID,
   CASE 
      WHEN ID != 2 THEN 'Others'
      ELSE ColorName
   END AS Name
FROM ColorTable

This gives me the following result:
ID - Name 
1 - Others
2 - Red
3 - Others
4 - Others

My requirement is to increment 'Others' by a value by one:
ID - Name 
1 - (1)Others
2 - Red
3 - (2)Others
4 - (3)Others

I have tried the following code but it failed:
DECLARE @Count INT = 1
SELECT 
   ID,
   CASE 
      WHEN ID != 2 THEN '(' + CONVERT(varchar(10), @Count)   + ')Others' SET @Count = @Count + 1
      ELSE ColorName
   END AS Name  
FROM ColorTable

Any suggestions? How will it give such result?

A:

While I agree that you should do this in the presentation layer, you could do it using ROW_NUMBER:
SELECT 
    CASE WHEN ID = 2 
        THEN ColorName 
        ELSE '(' + CAST(
            ROW_NUMBER() OVER 
            (
                PARTITION BY CASE WHEN ID = 2 THEN 0 ELSE 1 END 
                ORDER BY ID
            ) AS varchar) + ')Others' 
    END AS Name
FROM ColorTable
ORDER BY ID

