How to output duplicate patients side by side

Question

I have this data:

last name, first name, dob, id
gomez, allan, 10/1/2000, 1
gomez, allan, 10/1/2000, 2

The output I want:

col1 pat1 pat2
------------- ----------- ------------
last name gomez gomez
first name allan allan
dob 10/1/2000 10/1/2000
id 1 2

The patient column "pat1" will be dynamic, so it can have pat1, pat2, pat3 if there are multiple duplicates.

Do you want this side by side output as result of your SQL query? — GJBisschop, Nov 01 '19 at 17:08
Hi, it will be easier for the person to identify if the data can be merge if duplicate matches demographic information. So, I want the output side by side output as a result. thanks — JumarT, Nov 01 '19 at 17:19

score 0 · Answer 1 · answered Nov 01 '19 at 18:13

to check if you have patients duplicate you need to select a column to check it using HAVING

SELECT * FROM PATIENTS WHERE [last name] IN (
    SELECT [last name]
    FROM patients
    GROUP BY [last name]
    HAVING COUNT([last name]) > 2;
)

to put it in the output format that you want you could use a temp table using something like this Creating and Selecting table variables dynamically in SQL Server stored procedure?

but i will not recommend you to do this because is very difficult and unnecessary

score 0 · Answer 2 · answered Nov 01 '19 at 18:24

I don't think this is possible, given the output you want.

For example, this data won't be possible to put in the output format you want:

last name, first name, dob, id
gomez, allan, 10/1/2000, 1
gomez, allan, 10/1/2000, 2
doe, john, 10/1/2000, 3
doe, john, 10/1/2000, 4

That would result into:

col1 pat1 pat2 pat3 pat4
------------- ----------- ------------
last name gomez gomez doe doe
first name allan allan john john
dob 10/1/2000 10/1/2000 10/1/2000 10/1/2000
id 1 2 3 4

Which is not very readable.

To retrieve all duplicates you could use a query like:

SELECT [last name], [first name], dob, COUNT(1) [duplicate count]
FROM patient
GROUP BY [last name], [first name], dob
HAVING COUNT(1) > 1

This would return:

last name, first name, dob, duplicate count
gomez, allan, 10/1/2000, 2
doe, john, 10/1/2000, 2

How to output duplicate patients side by side

2 Answers2