SQL Search for duplicates, concatenate cells

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SQL Search for duplicates, concatenate cells

Author

Topic

wr3ckin.cr3w
Starting Member

2 Posts

Posted - 2010-09-24 : 15:05:27

Hello all,

I have a database where I need to find duplicate rows based on one cell and concatenate the two cells from both into one record. For example i'll have:


ID             NameOne               IDNumber

1              John                  2983

2              Jones                 2983

3              James                 2984

I'm wondering if there is a SQL statement where I can search for the duplicates of "2983" and come out with a single record of:


ID             NameOne               IDNumber

1              John Jones              2983

2              James                   2984

I've tried loading this into a Datatable and searching through it, but I get out of bounds errors when I try to jump ahead in the search for the next IDNumber to match with the current one. So i'm resorting to an SQL statement (which by searching the net I figure its possible) Thanks in advance!

X002548
Not Just a Number

15586 Posts

Posted - 2010-09-24 : 15:11:01

What if there are 10 rows with the same ID number?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

wr3ckin.cr3w
Starting Member

2 Posts

Posted - 2010-09-24 : 15:14:43

There won't be ;)

Max is 3 duplicate rows.

X002548
Not Just a Number

15586 Posts

Posted - 2010-09-24 : 15:46:57

unless you have a trigger that prevents that, don't say never...

In any case...



CREATE TABLE myTable99(ID int, NameOne varchar(30), IDNumber int)
GO

INSERT INTO myTable99(ID, NameOne, IDNumber)
SELECT 1, 'John',   2983 UNION ALL
SELECT 2, 'Paul',   2983 UNION ALL
SELECT 3, 'James',  2984 UNION ALL
SELECT 4, 'Lady',   2985 UNION ALL
SELECT 5, 'Jones',  2983 UNION ALL
SELECT 6, 'Gaga',   2985
GO

   SELECT xxx.IDNumber, s.NameOne, m.NameOne, e.NameOne
     FROM (SELECT IDNumber, MIN(ID) AS MIN_ID, MAX(ID) AS MAX_ID
			 FROM myTable99
		 GROUP BY IDNumber) AS XXX
LEFT JOIN myTable99 s ON s.IDNumber = XXX.IDNumber
      AND s.ID = xxx.MIN_ID
LEFT JOIN myTable99 m ON m.IDNumber = XXX.IDNumber
      AND m.ID > xxx.MIN_ID AND m.ID < xxx.MAX_ID
LEFT JOIN myTable99 e ON e.IDNumber = XXX.IDNumber
      AND e.ID = xxx.MAX_ID
      AND xxx.MIN_ID <> xxx.MAX_ID 
GO

DROP TABLE myTable99
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-09-28 : 11:32:25

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources