CODEWITHSUNDEEP
sqlt-sqlcaserowcount
AceAlfred
AceAlfred

Reputation: 1181

Case when rowcount

 SELECT distinct ID from table

Result

    Letter
1   A
2   B

How can I make the select display the following when the result is as above:

What I was thinking but not working yet:

SELECT CASE WHEN @@ROWCOUNT(ID) = 2 THEN 'AB'
ELSE ID END AS ID
FROM table

Result would be

     Letter
1    AB

Upvotes: 0

Views: 4362

Answers (3)

Ryan Nigro
Ryan Nigro

Reputation: 4619

Try:

Select
  CASE WHEN (ROW_NUMBER() OVER (ORDER BY [ID])) = 2 THEN 'AB'
  ELSE [ID] END AS [ID]
FROM table
group by
  [ID]

What's above will also only work if [ID] and 'AB' are the same data type. If not, then you'll need to cast [ID] to a varchar in your ELSE statement. That'd be: ELSE cast([ID] as varchar(8)) END AS [ID]

Upvotes: 1

gvee
gvee

Reputation: 17161

Your requirements really aren't clear.

Based on various assumptions, this might give you what you want.

SELECT Count(*) OVER (PARTITION BY 937)
     , CASE WHEN Count(*) OVER (PARTITION BY 937) = 2 THEN
         'AB'
       ELSE
         id
       END As id
     , id
FROM   your_table

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93694

Am not sure what you are trying to achieve. But to me it looks like this is what you are trying..

Declare a Variable and initialize it with Distinct Count of ID and use it in select

Declare @cnt int

SELECT @cnt=count(distinct ID) from table

SELECT CASE WHEN @cnt = 2 THEN 'AB'
ELSE ID END AS ID
FROM table

Upvotes: 0

Related Questions