Remove duplicate data from query results

Question

I have the following query:

 select C.ClientID, C.FirstName + ' ' + C.LastName as ClientName, CAST(V.StartDate as date) as VisitDate, count(*) as 'Number of Visits' from Visit V Inner Join Client C on V.ClientID = C.ClientID group by C.ClientID, C.FirstName + ' ' + C.LastName, CAST(V.StartDate as date) having count(*) > 3 order by C.ClientID, CAST(V.StartDate as date)

which gives the following results (names are fake in case anyone is wondering)

 ClientID ClientName VisitDate Number of Visits 75 Kay Taylor 2016-06-07 4 372 Moses Mcgowan 2016-09-03 4 422 Raven Mckay 2016-03-11 4 422 Raven Mckay 2016-06-14 4 679 Ulysses Booker 2016-01-09 4 696 Timon Turner 2016-07-06 4 1063 Quyn Wall 2016-06-25 4 1142 Garth Moran 2016-11-20 4 1142 Garth Moran 2016-11-21 4 1563 Hedley Gutierrez 2016-01-07 4 1563 Hedley Gutierrez 2016-01-17 4 1563 Hedley Gutierrez 2016-01-21 4 1563 Hedley Gutierrez 2016-01-27 4 1563 Hedley Gutierrez 2016-01-28 4 1563 Hedley Gutierrez 2016-01-30 4 1563 Hedley Gutierrez 2016-02-27 4 1563 Hedley Gutierrez 2016-03-26 4 1563 Hedley Gutierrez 2016-04-06 4 1563 Hedley Gutierrez 2016-04-09 4 1563 Hedley Gutierrez 2016-04-22 4 1563 Hedley Gutierrez 2016-05-06 4 1563 Hedley Gutierrez 2016-05-26 4 1563 Hedley Gutierrez 2016-06-02 4 1563 Hedley Gutierrez 2016-07-14 4 1563 Hedley Gutierrez 2016-07-29 4 1563 Hedley Gutierrez 2016-08-09 7 1563 Hedley Gutierrez 2016-09-01 4 1563 Hedley Gutierrez 2016-09-23 4 1563 Hedley Gutierrez 2016-12-07 4 1636 Kiara Lowery 2016-01-12 4 2917 Cynthia Carr 2016-06-21 4 2917 Cynthia Carr 2016-10-21 4 3219 Alan Monroe 2016-01-02 4 3219 Alan Monroe 016-02-27 4 3219 Alan Monroe 2016-09-01 5 4288 Natalie Mitchell 2016-03-19 4

How can I get the results to show only the ClientID and ClientName once so the results are like this?

 ClientID ClientName VisitDate Number of Visits 75 Kay Taylor 2016-06-07 4 372 Moses Mcgowan 2016-09-03 4 422 Raven Mckay 2016-03-11 4 2016-06-14 4 679 Ulysses Booker 2016-01-09 4 696 Timon Turner 2016-07-06 4 1063 Quyn Wall 2016-06-25 4 1142 Garth Moran 2016-11-20 4 2016-11-21 4 1563 Hedley Gutierrez 2016-01-07 4 2016-01-17 4 2016-01-21 4 2016-01-27 4 2016-01-28 4 2016-01-30 4 2016-02-27 4 2016-03-26 4 2016-04-06 4 2016-04-09 4 2016-04-22 4 2016-05-06 4 2016-05-26 4 2016-06-02 4 2016-07-14 4 2016-07-29 4 2016-08-09 7 2016-09-01 4 2016-09-23 4 2016-12-07 4 1636 Kiara Lowery 2016-01-12 4 2917 Cynthia Carr 2016-06-21 4 2016-10-21 4 3219 Alan Monroe 2016-01-02 4 3219 016-02-27 4 2016-09-01 5 4288 Natalie Mitchell 2016-03-19 4

I agree with HB, this is better handled at the presentation layer. Reporting tools will do this automatically with the right setting. — Joe C
– Joe C, Commented Feb 14, 2017 at 13:00
That would be ideal, but unfortunately this is a dataset that needs to go straight to an Excel spreadsheet so there's no reporting layer here and the results can be in the thousands so it can't be done manually. — Philip
– Philip, Commented Feb 14, 2017 at 13:04

Shaneis · Accepted Answer · 2017-02-17 14:56:55Z

Actually, what you want is not to remove duplicates, but not display them.

In order to do this you can use a CASE statement with ROW_NUMBER() and show the value on the 1st row and display either NULL or '' on the ELSE branch (the other rows):

select CASE WHEN ROW_NUMBER() OVER (PARTITION BY C.ClientID ORDER BY CAST(V.StartDate as date) ASC) = 1 THEN C.ClientID ELSE NULL END as ClientID, CASE WHEN ROW_NUMBER() OVER (PARTITION BY C.ClientID ORDER BY CAST(V.StartDate as date) ASC) = 1 THEN C.FirstName + ' ' + C.LastName ELSE NULL END as ClientName, CAST(V.StartDate as date) as VisitDate, count(*) as 'Number of Visits' from Visit V Inner Join Client C on V.ClientID = C.ClientID group by C.ClientID, C.FirstName + ' ' + C.LastName, CAST(V.StartDate as date) having count(*) > 3 order by C.ClientID, CAST(V.StartDate as date)

Dheeraj Sharma · Accepted Answer · 2017-02-17 14:45:36Z

Try this:

DECLARE @Table TABLE (ClientId NVARCHAR(5), ClientName NVARCHAR(6), VisitDate DATE, NumOfVisits INT) INSERT INTO @Table VALUES ('75' , 'A_NAME' , '2016-06-07' , '4' ),('372' , 'B_NAME' , '2016-09-03' , '4' ), ('422' , 'C_NAME' , '2016-03-11' , '4' ),('500' , 'D_NAME' , '2016-03-15' , '4'), ('500' , 'D_NAME' , '2016-03-19' , '4' ),('500' , 'D_NAME' , '2016-03-20' , '4'), ('500' , 'D_NAME' , '2016-07-15' , '4' ),('500' , 'D_NAME' , '2016-09-13' , '4'), ('600' , 'E_NAME' , '2016-03-19' , '4' ),('600' , 'E_NAME' , '2016-03-20' , '4'), ('600' , 'E_NAME' , '2016-07-15' , '4' ),('600' , 'E_NAME' , '2016-09-13' , '4') ;WITH A AS ( SELECT ROW_NUMBER() OVER(PARTITION BY ClientID ORDER BY ClientID) row_id,* FROM ( ----------------------------------------- SELECT * FROM @Table --- replace this line with your query---- ----------------------------------------- ) Main_Result ) SELECT ISNULL(BB.ClientID,'')ClientID,ISNULL(BB.ClientName,'')ClientName,AA.VisitDate,AA.NumOfVisits FROM A AA LEFT JOIN (SELECT * FROM A BB WHERE BB.row_id=1) BB ON AA.ClientID = BB.ClientID AND AA.row_id =BB.row_id ORDER BY CONVERT(INT,AA.ClientID)

Hope this helps. :)

you can execute this directly to get sample result from sample data. :)

Community · Accepted Answer · 2017-05-23 12:09:23Z

You can solved this with GROUP BY, grouping by (ClientID, VisitDate).

See the response 1097 here: Using group by on multiple columns

Note: In your ORDER BY is not necessary to use CAST(V.StartDate as date) you can use VisitDate because it exists in your SELECT: ... CAST(V.StartDate as date) as VisitDate,

EDIT: Try this:

SELECT C.ClientID, C.FirstName + ' ' + C.LastName as ClientName, CAST(V.StartDate as date) as VisitDate, count(*) as 'Number of Visits' from Visit V Inner Join Client C on V.ClientID = C.ClientID group by (C.ClientID, VisitDate) having count(*) > 3 order by C.ClientID, VisitDate

How's that going to help? Sorting on a varchar column can indeed lead to odd sortings, it depends what format the date is in. If you want to sort by date, casting to a date can make a difference.
This is not what is asked at all, the question is to show the name and id just for one record, and leave it empty for the other rows with the same name/id
Ok. Then i think you need to use string concatenation for the columns VisitDate and Number of Visits. After, you can split each value in different rows. See: [link]codeproject.com/articles/691102/… -stackoverflow.com/questions/17591490/…

Arockia Nirmal · Accepted Answer · 2017-02-23 12:27:44Z

I hope the below query would do that.... :)

WITH CTE AS ( select top 100 percent cast(C.ClientID as nvarchar(255)) as ClientID, C.FirstName + ' ' + C.LastName as ClientName, CAST(V.StartDate as date) as VisitDate, count(*) as 'Number of Visits', row_number() over (partition by C.ClientID,C.FirstName + ' ' + C.LastName ORDER BY CAST(V.StartDate as date) ) as rw_num from Visit V Inner Join Client C on V.ClientID = C.ClientID group by C.ClientID, C.FirstName + ' ' + C.LastName, CAST(V.StartDate as date) having count(*) > 3 order by min(C.ClientID), min(CAST(V.StartDate as date)) ) select case when rw_num<>1 then '' else ClientID end as ClientID, case when rw_num<>1 then '' else ClientName end as ClientName, VisitDate, [Number of Visits] from CTE

Result:

My test Data in my test tables:

Gerald R Spangler · Accepted Answer · 2017-02-23 20:01:45Z

I would use your initial query as a CTE or as subquery to replace #TMP_DATA. Below is how I would do this. Using a CASE with the LEAD function to determine if the data in ClientID and ClientName should be displayed:

SELECT CASE WHEN CAST(LAG(T.ClientID,1,'') OVER (PARTITION BY T.ClientID ORDER BY T.ClientID,T.VisitDate) AS VARCHAR) = T.ClientID THEN '' ELSE CAST(T.ClientID AS VARCHAR) END AS ClientID, CASE WHEN LAG(T.ClientName,1,'') OVER (PARTITION BY T.ClientID ORDER BY T.ClientID,T.VisitDate) = T.ClientName THEN '' ELSE T.ClientName END ClientName, T.VisitDate, T.[Number of Visits] FROM #TMP_DATA AS T

The result set is: !https://i.sstatic.net/MBfEn.png

Collectives™ on Stack Overflow

Remove duplicate data from query results

5 Answers 5

Comments

Comments

3 Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

3 Comments

Comments

Comments

Linked

Related