duplicate rows

Hi,

I have the following records in my sql table and want to place all the duplicates in one row. I tried to do this with an update query but had no success. I can do this in access but can't figure out how to do it in sql. Any help would be great. Thanks.

qrycurrentrecords LNAME MAJOR PAPATHANASIOU 135 DEPASSQUALLO 147 BILGER 215 KLER 267 MAKO 305 PERRY 379 MILLER 379 BILLS 379 WANDER 424 FLANAGAN 440 KAUFFMAN 440 KALLIS 492 SHARKY 670

mr4100 wrote:

... want to place all the duplicates in one row.

Can you explain a litlle more about what you mean?

|||

if you take a look at the picture, you can see there are 3 codes of 379. there are other columns attached to this table i just didn't show all of them. I want to loop through the table and place the 3 rows with the same code in a row by themselves. I'm sending an email from the table and don't want to send 3 seperate emails, just one email containing data about all 3 rows. My task sends an email for each row in the table. I hope I didn't confuse you.

thanks,

|||

It is still not clear what your desired output may look like. I suggest that if would be helpful if you posted the table DDL, some sample data in the form of INSERT statements, and what the desired output looks like. Also the version of SQL Server would be helpful in finding a solution.

I'm assuming you have explored the various ways to use GROUP BY...

|||

The confusing part is when you say: duplicates. There are not duplicates here, but rather it is just a typical situation with (hopefully) a parent table and key (for major) and then the child rows with different names. The best resource for how to do this is here:

http://databases.aspfaq.com/general/how-do-i-concatenate-strings-from-a-column-into-a-single-row.html

The site is sadly an eyesore these days, but the information is still good. Use the 2005 version for sure if you are using 2005, it is really excellent and works nice.

|||

what i want to do is take these 3 seperate rows that are in the table below with the same major and put them into 1 row by themselves whether it would be updating this table or a new table by themselves,

start with this:

name major desc

john 45 eng.

mary 45 eng.

corey 25 math

rose 45 eng.

sue 15 mus.

end with this:

name major desc

john,mary,rose 45 eng.

corey 25 math

sue 15 mus.

|||

Here it is.. if you use sql server 2005

Code Snippet

Create Table #data (

[name] Varchar(100) ,

[major] Varchar(100) ,

[desc] Varchar(100)

);

Insert Into #data Values('john','45','eng.');

Insert Into #data Values('mary','45','eng.');

Insert Into #data Values('corey','25','math');

Insert Into #data Values('rose','45','eng.');

Insert Into #data Values('sue','15','mus.');

Select Distinct

Substring((Select ',' + name [text()] from #data sub where sub.major=main.major and sub.[desc]=main.[desc] For Xml Path('')),2,8000)

,major

,[desc]

from

#data main

|||

this works perfectly! Could you explain why you use the #data sub and #data main and for xml Path(")),2,8000)?

I would just like to know what it is does to make this work, I would have never figured this out.

Thanks,