Duplicated name -

Question

Hello

In a simple query:

SELECT REFNO, FIRSTNAME, LASTNAME
FROM TABLE
WHERE...

I often see duplicated names. So for example the output can be:

REFNO FIRSTNAME LASTNAME

1 BILL FAULKNER
2 BILL FAULKNER

I would like to write a report that highlights these duplicate entries but which keeps the output in one row,

So the desired output would be:

REFNO1 REFNO2 FIRSTNAME LASTNAME
1 2 BILL FAULKNER

Please can you advise how I'd go about achieving this?

With many thanks.

Answer 1

Hi @Will Faulkner ，

Please refer to:

declare @test table (REFNO int, FIRSTNAME char(15),  LASTNAME char(15))  
INSERT INTO @test values(1,'BILL','FAULKNER'),(2 ,'BILL', 'FAULKNER')  
  
SELECT FIRSTNAME, LASTNAME  
       , STUFF((SELECT ',' + CAST(REFNO AS VARCHAR(30)) AS [text()]  
          FROM @test AS O  
          WHERE O.FIRSTNAME = C.FIRSTNAME and O.LASTNAME = C.LASTNAME  
          FOR XML PATH('')), 1, 1, NULL) AS REFNO  
FROM @test  AS c  
GROUP BY FIRSTNAME, LASTNAME  

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

If there are no more than two duplicate names in each case, then, check this query too:

select t1.REFNO as REFNO1, t2.REFNO as REFNO2, t1.FIRSTNAME, t2.LASTNAME
from MyTable as t1 
inner join MyTable as t2 on t2.FIRSTNAME = t1.FIRSTNAME and t2.LASTNAME = t1.LASTNAME and t2.REFNO > t1.REFNO
order by t1.REFNO

Answer 3

Hi @Will Faulkner ，

Please check another method：

    declare @test table (REFNO int, FIRSTNAME char(15),  LASTNAME char(15))  
    INSERT INTO @test values(1,'BILL','FAULKNER'),(2 ,'BILL', 'FAULKNER')  
      
    ;with cte  
    as(SELECT *,row_number() over(partition by FIRSTNAME,LASTNAME order by REFNO)rn from @test)  
    select c1.REFNO REFNO1,c2.REFNO REFNO2,c1.FIRSTNAME,c1.LASTNAME from cte c1  
    join cte c2 on c1.rn=c2.rn-1 and c1.FIRSTNAME=c2.FIRSTNAME and c1.LASTNAME=c2.LASTNAME  

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

Does below query work?

CREATE TABLE Mytable
    ([REFNO] int, [FIRSTNAME] varchar(4), [LASTNAME] varchar(8))
;

INSERT INTO Mytable
    ([REFNO], [FIRSTNAME], [LASTNAME])
VALUES
    (1, 'BILL', 'FAULKNER'),
    (2, 'BILL', 'FAULKNER'),
    (3, 'Joey', 'Bing'),
    (4, 'Joey', 'Bing')
;

select STRING_AGG(RefNo,',') NewColumn,FIRSTNAME, LASTNAME from Mytable
group by FIRSTNAME, LASTNAME

---

Please don't forget to Accept Answer and Up-vote if the response helped -- Vaibhav

Answer 5

What version of SQL Server are you on? STRING_AGG() might be used, but that was introduced in 2016, I believe.