SQL - different Id with same value then True without subquery-CodePudding

I try to transform following table without a subquery:

Document     Reader 
doc1         John
doc2         Max
doc2         John

This would be my desired result :

Reader      Duplicate
John        True
Max         False

CodePudding user response：

Use group by to get the readers distinct, and count in a case to determine they are duplicate

declare @t table (Document varchar(10), Reader varchar(10))
insert into @t values ('doc1', 'John'), ('doc2', 'Max'), ('doc2', 'John')

select t.Reader, 
       case when count(Document) > 1 then 'True' else 'False' end as Duplicate
from   @t t
group by t.Reader

And if you need Duplicate as a bit column, than use this

select t.Reader, 
       convert(bit, case when count(Document) > 1 then 1 else 0 end) as Duplicate
from   @t t
group by t.Reader

You can try it out yourself in DBFiddle

CodePudding user response：

SELECT Reader, CASE WHEN COUNT(Reader)>1 Then 'True' Else 'False' END Duplicate 
FROM <your_table>
Group By Reader;