Click here to Skip to main content
15,883,458 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
i have a table which may contain few duplicate values like

id.................fullname.......... username....... dept
1.................wayne roo.........rooney...........OT
2.................wayne roo.........wayne............RnD
3.................alex gill..........alexis...............xyz
4.................harry dsdsd............devil...............abc
5................ harry kath............kathhh.........gggg

now i want to fetch only those records with same full name.

1.................wayne roo.........rooney...........OT
2.................wayne roo.........wayne............RnD.

and i want to concat user name with full name for the records with same full name
like

wayne roo[rooney]
wayne roo[wayne]
alex gill
harry dsdsd
harry kath

how do i do this.. pls help...
thanks in advance
Posted
Updated 3-Aug-11 1:25am
v2

SELECT fullname as fname INTO #temp1 FROM TestTable
GROUP BY 
    fullname
HAVING 
    (COUNT(fullname) > 1); 

SELECT (fullname+ '[' + username + ']') from TestTable thetest
right JOIN #temp1 T on thetest.fullname = T.fname

UNION

SELECT fullname
FROM TestTable
GROUP BY fullname
HAVING ( COUNT(fullname) = 1 );

drop TABLE #temp1;
 
Share this answer
 
Comments
greatreddevil 4-Aug-11 0:00am    
it works thanks... :)
UJimbo 4-Aug-11 2:46am    
You are welcome. Take care :)
select fullname||username from table where fullname in(
SELECT fullname FROM Table
group by fullname having count(*)>1)
union
select fullname from table 
group by fullname having count(*)=1



Try it !
 
Share this answer
 
v2
Comments
greatreddevil 4-Aug-11 0:00am    
this works.... thanks a lot :)
try something like

Select fullname, username, dept from tablename tn
Join tablename on tn.fullname = tablename.fullname and tn.id <> tablename.id
 
Share this answer
 
Comments
UJimbo 3-Aug-11 9:13am    
Your T-SQL is only showing the duplicates, ignoring the records appearing one time.
Paul E Davies 3-Aug-11 9:40am    
I thought that's what the OP asked for

now i want to fetch only those records with same full name.

1.................wayne roo.........rooney...........OT
2.................wayne roo.........wayne............RnD.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900