Concatenating duplicate rows - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Concatenating duplicate rows

Author

Topic

sfactor27
Starting Member

2 Posts

Posted - 2009-11-11 : 17:49:05

Hi,

I was hoping to see if anyone would be able to suggest a solution to an issue I'm having. Essentially, I have a table of documents approx 670 of which there are 170 duplicates of the record_number.

The duplicate rows are almost identical except for an author column. Now instead of allowing duplicates I want to append the authors into one of the records and delete the other.

Here is the code I employed to determine the count for duplicates to distinguish the duplicates from the unique records in the table:

Exec('create view record_number as select record_number from x_cp_docs;')
update x_cp_docs 
set x_cp_docs.doc_version = 
(select count(record_number.record_number) 
from record_number 
where x_cp_docs.record_number = record_number.record_number);

This has left me with the following.

record_number doc_author doc_version
D03/135 John Smith 2
D03/135 Lesley Jones 2
D03/136 William Shaw 2
D03/136 Janet Goldberg 2

Ideally, I would like to be able to achieve this:

record_number doc_author
D03/135 John Smith / Lesley Jones
D03/136 William Shaw / Janet Goldberg

Any help would be appreciated.

Cheers

sfactor27
Starting Member

2 Posts

Posted - 2009-11-11 : 23:50:31

I have since found a work-around.. thank you
If anyone is interested this was the temp solution:

create table ntest (recno integer, recauth varchar(20));

add some values

insert into ntest values (1, 'a');
insert into ntest values (2, 'b');
insert into ntest values (2, 'c');
now, the following query gets the unique record numbers (recno) and concatenates the record author fields into a single result

select distinct t.recno, stuff(( select distinct ' '+ recauth from ntest as t1 where t1.recno = t.recno for xml path('')),1,1,'') from ntest as t;
The output is:

1 a
2 b c

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-12 : 01:16:12

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