Merge apparent duplcate rows into 1 row?

Hi,
I have a select query that can generate apparent duplicates; this
occurs because the Histology value is determined from a table
tblSample, this may contain a number of samples for the same location
as there are different methods of obtaining samples sometimes 2 or more
method are used to back up results. The method is not important for
this table and so not shown, so showing apparent duplicates. Heres an
example of the table:
Code Date Location Histology
---
CO123 12/08/2005 Left Main Adeno
CO123 12/08/2005 Left Main Adeno
BJ234 12/08/2005 Right Main Normal
BJ234 12/08/2005 Right Lower Squamous
CH345 17/08/2005 Right Middle Normal
This is my SQL:
SELECT tblPatient.pntCode AS Code, tblPDT.pdtDate AS Date,
tblLesion.lesLocation AS Location,
tblSample.splHistology AS Histology
FROM tblPatient, tblPDT, tblLesion, tblSample
WHERE tblPatient.patientNo = tblPDT.patientNo
AND tblPatient.patientNo = tblLesion.patientNo
AND tblLesion.lesNo = tblSample.lesNo
Is there a way to combine these apparent duplicate rows into one row?
Essentially doing:
If no of rows where Code, Date, Location, match > 1
Delete rows >= 2
ThanksUse a SELECT DISTINCT:
SELECT DISTINCT
tblPatient.pntCode AS Code, tblPDT.pdtDate AS Date,
tblLesion.lesLocation AS Location,
tblSample.splHistology AS Histology
FROM tblPatient, tblPDT, tblLesion, tblSample
WHERE tblPatient.patientNo = tblPDT.patientNo
AND tblPatient.patientNo = tblLesion.patientNo
AND tblLesion.lesNo = tblSample.lesNo
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Assimalyst" <c_oxtoby@.hotmail.com> wrote in message
news:1125055041.994620.74900@.g14g2000cwa.googlegroups.com...
Hi,
I have a select query that can generate apparent duplicates; this
occurs because the Histology value is determined from a table
tblSample, this may contain a number of samples for the same location
as there are different methods of obtaining samples sometimes 2 or more
method are used to back up results. The method is not important for
this table and so not shown, so showing apparent duplicates. Heres an
example of the table:
Code Date Location Histology
---
CO123 12/08/2005 Left Main Adeno
CO123 12/08/2005 Left Main Adeno
BJ234 12/08/2005 Right Main Normal
BJ234 12/08/2005 Right Lower Squamous
CH345 17/08/2005 Right Middle Normal
This is my SQL:
SELECT tblPatient.pntCode AS Code, tblPDT.pdtDate AS Date,
tblLesion.lesLocation AS Location,
tblSample.splHistology AS Histology
FROM tblPatient, tblPDT, tblLesion, tblSample
WHERE tblPatient.patientNo = tblPDT.patientNo
AND tblPatient.patientNo = tblLesion.patientNo
AND tblLesion.lesNo = tblSample.lesNo
Is there a way to combine these apparent duplicate rows into one row?
Essentially doing:
If no of rows where Code, Date, Location, match > 1
Delete rows >= 2
Thanks|||Thanks Tom, i'm pretty new to SQL so not very familiar with the syntax
yet. Glad this one was an easy fix!|||If you're new to SQL, this is the place to hang out. :-)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Assimalyst" <c_oxtoby@.hotmail.com> wrote in message
news:1125058223.511501.191000@.o13g2000cwo.googlegroups.com...
Thanks Tom, i'm pretty new to SQL so not very familiar with the syntax
yet. Glad this one was an easy fix!