OK, i had a scenario where the database contained data with non-unique names, but we wanted to force the names to appear unique.  For example, if you had “TheThing” and it was repeated 3 times, we’d want “TheThing(1)”, “TheThing(2)” and “TheThing(3)”.  I had to do this all in Tsql since its in a SSRS report and that’s the clearest way to do this for the given situation.

So, I’m sure there are lots of other ways to do this (feel free to supply those via comments), but I used a temp table, and the tsql RANK() function to do it: 

CREATE

TABLE #DuplicateNamesWorkAround(Id int, Name nvarchar(100),DuplicateExists bit,NameRank int)

INSERT INTO#DuplicateNamesWorkAround
SELECT t.id, t.Name,
(SELECT CASE
WHEN COUNT(*) >1 THEN 1 ELSE 0 END
FROM TableWithDups t
WHERE t.Name=t.Name) AS DuplicateExists,
— We’re creating a rank partition made up of the name with a hard-coded suffix.
— By doing so, we can get a rank thats unique per partition, and not across the
— entire resultset (since we want to append rank numbers to the name to give
— unique names).
RANK() OVER(PARTITION BY(
SELECT CASE WHEN COUNT(*) >1 THEN t.Name + ‘DUPLICATE’ ELSE t.Name END
FROM
TableWithDups t
WHERE t.Name=t.Name group by t.Name

 UPDATE #DuplicateNamesWorkAround
   SET Name=Name + ‘(‘ + CAST(NameRank AS VARCHAR(2)) + ‘)’ WHERE DuplicateExists=1

Nothing amazing, and perhaps there are better ways with counters and cursors, but this worked for me.