Find duplicates in SQL table, list multiple instances

Question

I'm trying to list the multiple instances of tbldoc.[docid] from tbldoc where tbldoc.[filename] occurs more than once, id like them seperated by comma and grouped by [filename]

this code works great to find duplicates:
SELECT cast([filename] as varchar(max)),  
 COUNT(cast([filename] as varchar(max))) 
FROM tbldoc
GROUP BY cast([filename] as varchar(max))
HAVING ( COUNT(cast([filename] as varchar(max))) > 1 )

but when i try adding [docid] i get an error:

Column 'tbldoc.DocID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

this is what i am trying:

SELECT [docid], cast([filename] as varchar(max)),  
 COUNT(cast([filename] as varchar(max))) 
FROM tbldoc
GROUP BY cast([filename] as varchar(max))
HAVING ( COUNT(cast([filename] as varchar(max))) > 1 )

I have no idea how to get all of the [docid]s to list seperated by commas, I'm a a pretty new user when it comes to sql.

this is the output i would like to see:

[docids]|[filemame]|[instances]

12345,12346| excelfile.xls | 3

Thanks ahead of time for the help guys/gals! =)

How are there 3 instances of `excelfile.xls` but only 2 `docids`? Are there multiple entries with the same `docid`? — Hart CO, Apr 09 '14 at 21:50
This question may help you get that comma delimited list: http://stackoverflow.com/questions/1817985/how-do-i-create-a-comma-separated-list-using-a-sql-query — Adam Miller, Apr 09 '14 at 21:55
Goat, that was just a typed out example... a poor one at that, it should have showed 2 instances. — lyosha, Apr 14 '14 at 17:32

score 0 · Answer 1 · answered Apr 10 '14 at 04:53

Iyosha,

You need to join your first result set back to your full table to get the DocIDs. I'll take the CAST() as read to save some typing.

;with CountedFiles as
(
    SELECT
        filename,  
        COUNT(filename) as Total
    FROM tbldoc
    GROUP BY filename
    HAVING COUNT(filename) > 1
)
select
    cf.filename,
    cf.Total,
    td.DocID
from CountedFiles as cf
inner join tbldoc at td
    on td.filename = cf.filename;

This will return one DocId, one filename and the count per row. You can then follow Adam's link to turn this into a comma list.

Find duplicates in SQL table, list multiple instances

1 Answers1