Manipulating duplicate values?

Question

I have a table, with an ID, FirstName & Lastname.

I'm selecting that using the following query:

SELECT USER_ID as [ID], First_P + ' ' + Last_P as FullName FROM Persons

It's working fine. I'm basically having a list of ID's and full names.

Full names could be the same. How is it possible for me to find them and add the ID on the Full name cell as well? only when the names are the same.

Example:

1      John Wick (1) 
50     John Wick (50)

I haven't found any similar questions to be honest, at least not for MSSQL. So If there are any, feel free to link me.

Possible duplicate of [How to use GROUP\_CONCAT in a CONCAT in MySQL](https://stackoverflow.com/questions/13451605/how-to-use-group-concat-in-a-concat-in-mysql) — Peter Bode, Dec 23 '18 at 09:07
@PeterBode That's MySQL and GROUP_CONCAT in SQL-Server I believe. I'll give it a read though! — MHDGim, Dec 23 '18 at 09:14
Do you want the ID only when there are multiple people with the same name, or on every row? — Thom A, Dec 23 '18 at 09:24
@Larnu The one between brackets? I want it to show only when there are multiple people with the same name yes. — MHDGim, Dec 23 '18 at 09:25
It's worth showing expected results for both side of the coin then, and more than one example; as that makes it clear in your question your actual goal. Looks like you need a `CASE` expression and a `COUNT` with the `OVER` clause. I'm pretty sure there are lots of examples here on how to achieve this. Have a look at those functions and have a go; if you get stuck edit your attempts in and comment back. — Thom A, Dec 23 '18 at 09:36

Derviş Kayımbaşıoğlu · Accepted Answer · 2018-12-23T09:54:04.767

0

please take a look my answer. I used nested query to identify number of duplicated names

SELECT 
  ID,
  IIF(NUMBEROFDUPS =1, NAME, CONCAT(NAME, ' (', ID, ')')) AS NAME
FROM
(
  SELECT 
    ID,
    CONCAT(First_P, ' ', Last_P) AS NAME,
    COUNT(*) OVER (PARTITION BY First_P,Last_P) AS NUMBEROFDUPS 
  FROM 
    Table1
) tmp;

edited Dec 23 '18 at 09:54

answered Dec 23 '18 at 09:37

Derviş Kayımbaşıoğlu

28,492
4
50
72

I keep getting incorrect syntax with the last row (the `);`) and I can't figure out why. – MHDGim Dec 23 '18 at 09:46

score 0 · Answer 2 · answered Dec 23 '18 at 09:40

You can use outer apply to group the items via First_P + ' ' + Last_P

and then add case for multiple items.

The select stuff should look like:

   SELECT USER_ID as [ID], p1.First_P + ' ' + p1.Last_P + case when cnt.FullName is not null 
        then '(' + p2.[sum] + ')' else '' end as FullName FROM Persons p1
        outer apply (select First_P + ' ' + Last_P as FullName,count(1) as [sum]
                     from Persons p2
                     where p2.First_P + ' ' + p2.Last_P = p1.First_P + ' ' + p1.Last_P
                     group by First_P + ' ' + Last_P
                     having count(1) > 1) cnt

Manipulating duplicate values?

2 Answers2