Check duplicate names in SQL server table

Posted by Nandkishorrech under Sql Server on 8/28/2017 | Points: 10 | Views : 391 | Status : [Member] | Replies : 1
Hi all,
i have below table like this.
id name class
1 A null
2 B null
3 c null
4 d null
1 A 9
3 c 9

how to check data with bit value is exist or not. i need below output.

i tried this... but not working
SELECT Id,name,class
(case when isnull(class,123)=123 then 0 else 1 end) as exist
FROM test_Table.

i need below output
id name class exist
2 B null 0
4 d null 0
1 A 9 1
3 c 9 1

regards
Kishore




Responses

Posted by: Bandi on: 8/29/2017 [Member] [MVP] Platinum | Points: 25

Up
0
Down
declare @tab table(id int, name char(1),class int)
insert @tab
select 1, 'A', null union all
select 2 , 'B' ,null union all
select 3 , 'c' ,null union all
select 4 , 'd' ,null union all
select 1 , 'A' ,9union all
select 3 , 'c' ,9

SELECT Id,name, max(class) class, case when count(class) >=1 then 1 else 0 end as IsExists
FROM @tab
GROUP BY id, name

output
Id	name	class	IsExists
1 A 9 1
2 B NULL 0
3 c 9 1
4 d NULL 0


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Nandkishorrech, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response