CODEWITHSUNDEEP
sqlsql-servert-sql
TRam
TRam

Reputation: 11

Same account different different status then other else same status . How to do this in T-SQl

Same account different different status then other else same status . How to do this in T-SQl

Account Status
1000001185  A
1000001185  E 
1000001185  E 
1000001185  D
1000001777  E 
1000001777  E 
1000001777  E 
1000001185  E

Upvotes: 1

Views: 63

Answers (2)

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

You can do :

select Account, (case when min(Status) = max(Status) 
                      then min(Status) 
                      else 'other' 
                  end)
from table t
group by Account;

Here is a SQL Fiddle showing that this solution is correct.

Upvotes: 1

Sreenu131
Sreenu131

Reputation: 2516

Try this i used Lead () Function

;WITH CTE(Account, [Status])
AS
(
SELECT '1000001185','A' UNION ALL
SELECT '1000001185','E' UNION ALL 
SELECT '1000001185','E' UNION ALL 
SELECT '1000001185','D' UNION ALL
SELECT '1000001777','E' UNION ALL 
SELECT '1000001777','E' UNION ALL 
SELECT '1000001777','E' UNION ALL 
SELECT '1000001185','E' 
)
SELECT Account,[Status],CASE WHEN [Status]<>LEAD([Status],1)OVER(PARTITION BY Account ORDER BY Account)
            THEN 'Other' ELSE [Status] END [NewStatus]
 FROM Cte 

 ORDER BY cte.[Status]

Result

 Account    Status       NewStatus
 -------------------------------------
1000001185      A           A
1000001185      D           Other
1000001185      E           Other
1000001185      E           E
1000001185      E           Other
1000001777      E           E
1000001777      E           E
1000001777      E           E

Upvotes: 0

Related Questions