Trying to get one record out of multiple records that are caused by case when statements

Question

Trying to get one record out of multiple records that are caused by case when statements

NineIron

SSChampion

Points: 12537
More actions
August 2, 2013 at 6:55 am

#278171

Each patient in the table, TEST, can have more that one Ethnicity but, I would like all the data to show on only one record. Here's my query, along with some sample data.
declare@StartDate datetime,
@EndDate datetime
set @StartDate='2013-05-01'
set @EndDate='2013-06-30'
;with Patients
as
(
select SourceID, PatientID, AdmitDateTime, LocationID
from AbstractData a
where a.PtStatus='IN'
and a.AdmitDateTime>=@StartDate and a.AdmitDateTime<datediff(d, -1, @EndDate)
and a.LocationID!='A3/TCU'
)
select a.PatientID as 'Identifier', a.AdmitDateTime, month(AdmitDateTime) as 'MonthAdmitted', b.City, b.PostalCode as 'Zip', a.LocationID as 'InPatientUnit',
b.RaceName as 'Race', c.Response as 'PrimaryLanguage', d.Response as 'PreferredLanguage', e.Ethnicity, e.Ethnicity2, e.Ethnicity3
from Patients a
inner join MriDrcPatients b on
a.SourceID=b.SourceID
and a.PatientID=b.PatientID
left join (select SourceID, PatientID, Response
from MriPatientClinicalQueries
where QueryID='COMMBARR') c on
a.SourceID=c.SourceID
and a.PatientID=c.PatientID
left join (select SourceID, PatientID, Response
from MriPatientClinicalQueries
where QueryID='COMMBARR3') d on
a.SourceID=d.SourceID
and a.PatientID=d.PatientID
left join (select SourceID, PatientID,
case when QueryID='ETHN1' then Response end as 'Ethnicity',
case when QueryID='ETHN2' then Response end as 'Ethnicity2',
case when QueryID='ETHN3' then Response end as 'Ethnicity3'
from MriPatientClinicalQueries
where QueryID in ('ETHN1', 'ETHN2', 'ETHN3')
and Response is not null) e on
a.SourceID=e.SourceID
and a.PatientID=e.PatientID
order by a.PatientID, month(AdmitDateTime)
create table TEST
(
Identifier varchar(10),
Race varchar(10),
AdmitDateTime datetime,
MonthAdmitted int,
City varchar(100),
Zip varchar(10),
InPatientUnit varchar(5),
Race varchar(10),
PrimaryLanguage varchar(8),
Ethnicity varchar(50),
Ethnicity2 varchar(50),
Ethnicity3 varchar(50)
)
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '100276','WHITE','ENGL','ENGL','OTHOTHER','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '100276','WHITE','ENGL','ENGL','NULL','NULL','ALBANIAN')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '100328','WHITE','ENGL','ENGL','AMEAMERCN','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '100328','WHITE','ENGL','ENGL','NULL','NULL','SWEDISH')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '101163','WHITE','ENGL','ENGL','AMEAMERCN','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '101382','WHITE','ENGL','ENGL','ITA2108-9','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '101382','WHITE','ENGL','ENGL','NULL','IRI2108-9','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '101382','WHITE','ENGL','ENGL','ITA2108-9','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '101382','WHITE','ENGL','ENGL','NULL','IRI2108-9','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '101726','WHITE','ENGL','ENGL','AMEAMERCN','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '101773','WHITE','ENGL','ENGL','AMEAMERCN','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '10227','WHITE','ENGL','ENGL','AMEAMERCN','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '10272','UNKNOWN','ENGL','ENGL','IRI2108-9','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '103092','WHITE','ENGL','ENGL','IRI2108-9','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '104654','WHITE','ENGL','ENGL','AMEAMERCN','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '1052','WHITE','ENGL','ENGL','AMEAMERCN','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '105307','WHITE','ENGL','ENGL','IRI2108-9','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '105307','WHITE','ENGL','ENGL','NULL','AMEAMERCN','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '105410','WHITE','ENGL','ENGL','AMEAMERCN','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '105410','WHITE','ENGL','ENGL','NULL','IRI2108-9','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '107139','WHITE','ENGL','ENGL','AMEAMERCN','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '107466','WHITE','ENGL','ENGL','AMEAMERCN','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '108584','WHITE','ENGL','ENGL','SPA2108-9','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '111168','WHITE','ENGL','ENGL','OTHOTHER','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '111168','WHITE','ENGL','ENGL','NULL','NULL','JEWISH')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '11173','WHITE','ENGL','ENGL','PORPORTUG','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '11173','WHITE','ENGL','ENGL','PORPORTUG','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '112026','WHITE','ENGL','ENGL','AMEAMERCN','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '112821','WHITE','ENGL','ENGL','AMEAMERCN','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '113118','WHITE','ENGL','ENGL','AMEAMERCN','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '114061','WHITE','ENGL','ENGL','AMEAMERCN','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '115492','WHITE','ENGL','ENGL','AMEAMERCN','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '115492','WHITE','ENGL','ENGL','AMEAMERCN','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '11573','WHITE','ENGL','ENGL','AMEAMERCN','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '115744','WHITE','ENGL','ENGL','AMEAMERCN','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '11617','WHITE','ENGL','ENGL','ITA2108-9','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '11617','WHITE','ENGL','ENGL','NULL','AMEAMERCN','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '11617','WHITE','ENGL','ENGL','ITA2108-9','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '11617','WHITE','ENGL','ENGL','NULL','AMEAMERCN','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '116456','BLACK','ENGL','ENGL','AFR2058-6','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '116902','WHITE','ENGL','ENGL','AMEAMERCN','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '116927','WHITE','ENGL','ENGL','AMEAMERCN','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '117034','BLACK','ENGL','ENGL','AFR2058-6','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '117858','WHITE','ENGL','ENGL','AMEAMERCN','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '118817','WHITE','ENGL','ENGL','AMEAMERCN','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '119170','BLACK','ENGL','ENGL','AFR2058-6','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '119399','BLACK','ENGL','ENGL','AFR2058-6','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '120155','WHITE','ENGL','ENGL','AMEAMERCN','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '120495','WHITE','ENGL','ENGL','ENG2108-9','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '120547','WHITE','ENGL','ENGL','ITA2108-9','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '120774','WHITE','ENGL','ENGL','PORPORTUG','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '121387','WHITE','ENGL','ENGL','AMEAMERCN','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '121571','BLACK','ENGL','ENGL','AFR2058-6','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '12281','WHITE','ENGL','ENGL','AMEAMERCN','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '123138','WHITE','ENGL','ENGL','AMEAMERCN','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '123138','WHITE','ENGL','ENGL','AMEAMERCN','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '123138','WHITE','ENGL','ENGL','AMEAMERCN','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '125713','WHITE','ENGL','ENGL','GRE2108-9','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '125748','WHITE','ENGL','ENGL','AMEAMERCN','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '125901','WHITE','ENGL','ENGL','AMEAMERCN','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '126237','WHITE','ENGL','ENGL','AMEAMERCN','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '126308','WHITE','ENGL','ENGL','AMEAMERCN','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '126308','WHITE','ENGL','ENGL','AMEAMERCN','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '126443','BLACK','ENGL','ENGL','AFR2058-6','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '126993','WHITE','ENGL','ENGL','IRI2108-9','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '12822','WHITE','ENGL','ENGL','AMEAMERCN','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '128262','WHITE','ENGL','ENGL','POLEASTEU','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '128705','WHITE','ENGL','ENGL','AMEAMERCN','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '12911','WHITE','ENGL','ENGL','IRI2108-9','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '130487','WHITE','ENGL','ENGL','AMEAMERCN','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '130487','WHITE','ENGL','ENGL','AMEAMERCN','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '130804','WHITE','ENGL','ENGL','AMEAMERCN','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '130804','WHITE','ENGL','ENGL','NULL','IRI2108-9','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '131334','WHITE','ENGL','ENGL','AMEAMERCN','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '131702','WHITE','ENGL','ENGL','IRI2108-9','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '131702','WHITE','ENGL','ENGL','NULL','ITA2108-9','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '133413','BLACK','ENGL','ENGL','AFR2058-6','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '134153','WHITE','ENGL','ENGL','AMEAMERCN','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '134544','WHITE','ENGL','ENGL','AMEAMERCN','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '134544','WHITE','ENGL','ENGL','NULL','NULL','ARMENIAN')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '134803','WHITE','ENGL','ENGL','AMEAMERCN','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '134806','WHITE','ENGL','ENGL','AMEAMERCN','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '13605','WHITE','ENGL','ENGL','AMEAMERCN','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '136260','WHITE','ENGL','ENGL','ITA2108-9','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '136631','WHITE','ENGL','ENGL','AMEAMERCN','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '137608','WHITE','ENGL','ENGL','AMEAMERCN','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '138255','WHITE','ENGL','ENGL','EASEASTEU','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '139187','WHITE','ENGL','ENGL','ENG2108-9','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '139878','UNKNOWN','ENGL','ENGL','AMEAMERCN','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '140051','WHITE','ENGL','ENGL','SCO2108-9','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '140200','WHITE','ENGL','ENGL','PORPORTUG','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '140252','UNKNOWN','ENGL','ENGL','AMEAMERCN','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '141678','WHITE','ENGL','ENGL','AMEAMERCN','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '141685','WHITE','ENGL','ENGL','ENG2108-9','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '141905','WHITE','ENGL','ENGL','IRI2108-9','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '142028','WHITE','ENGL','ENGL','AMEAMERCN','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '142290','WHITE','ENGL','ENGL','AMEAMERCN','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '142290','WHITE','ENGL','ENGL','NULL','SCO2108-9','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '142414','WHITE','ENGL','ENGL','IRI2108-9','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '142414','WHITE','ENGL','ENGL','NULL','AMEAMERCN','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '143156','WHITE','ENGL','ENGL','AMEAMERCN','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '144571','WHITE','ENGL','ENGL','ENG2108-9','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '144571','WHITE','ENGL','ENGL','NULL','FRE2108-9','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '144571','WHITE','ENGL','ENGL','NULL','NULL','AMERICAN')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '145313','WHITE','ENGL','ENGL','AMEAMERCN','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '146018','WHITE','ENGL','ENGL','AMEAMERCN','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '146018','WHITE','ENGL','ENGL','AMEAMERCN','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '146352','WHITE','ENGL','ENGL','ITA2108-9','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '146352','WHITE','ENGL','ENGL','NULL','FRE2108-9','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '146402','WHITE','ENGL','ENGL','ENG2108-9','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '146402','WHITE','ENGL','ENGL','ENG2108-9','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '146842','WHITE','ENGL','ENGL','IRI2108-9','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '146842','WHITE','ENGL','ENGL','NULL','ENG2108-9','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '146842','WHITE','ENGL','ENGL','NULL','NULL','ITALIAN')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '147660','WHITE','ENGL','ENGL','AMEAMERCN','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '149444','WHITE','ENGL','ENGL','AMEAMERCN','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '149444','WHITE','ENGL','ENGL','AMEAMERCN','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '149444','WHITE','ENGL','ENGL','AMEAMERCN','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '149990','WHITE','ENGL','ENGL','AMEAMERCN','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '149990','WHITE','ENGL','ENGL','AMEAMERCN','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '150031','WHITE','ENGL','ENGL','FRE2108-9','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '150031','WHITE','ENGL','ENGL','NULL','AMEAMERCN','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '150362','WHITE','ENGL','ENGL','IRI2108-9','NULL','NULL')
insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '151270','WHITE','ENGL','ENGL','ENG2108-9','NULL','NULL')

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

Thanks for posting ddl and sample data. You posted a query which seems to have nothing to do with the sample table you posted? Also the table you posted doesn't work. There are two columns named "Race". You are inserting string literal NULL instead of actual NULLs. You have a column named "PreferredLanguage" list in the insert but that column doesn't exist in the table you posted. You should test the ddl and sample data before you post it.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

NineIron SSChampion Points: 12537 More actions · Answer 2

I'm such a rookie. So, assuming the code works now, the first patient, 100276, has two records with Ethnicity of ENGL and Ethnicity3 of ALBANIAN. I'd like the two records to show as one.

create table TEST

(

Identifier varchar(10),

Race varchar(10),

PrimaryLanguage varchar(8),

PreferredLanguage varchar(8),

Ethnicity varchar(50),

Ethnicity2 varchar(50),

Ethnicity3 varchar(50)

)

insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '100276','WHITE','ENGL','ENGL','OTHOTHER','','')

insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '100276','WHITE','ENGL','ENGL','','','ALBANIAN')

insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '100328','WHITE','ENGL','ENGL','AMEAMERCN','','')

insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '100328','WHITE','ENGL','ENGL','','','SWEDISH')

insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '101163','WHITE','ENGL','ENGL','AMEAMERCN','','')

insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '101382','WHITE','ENGL','ENGL','ITA2108-9','','')

insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '101382','WHITE','ENGL','ENGL','','IRI2108-9','')

insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '101382','WHITE','ENGL','ENGL','ITA2108-9','','')

insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '101382','WHITE','ENGL','ENGL','','IRI2108-9','')

insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '101726','WHITE','ENGL','ENGL','AMEAMERCN','','')

insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '101773','WHITE','ENGL','ENGL','AMEAMERCN','','')

insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '10227','WHITE','ENGL','ENGL','AMEAMERCN','','')

insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '10272','UNKNOWN','ENGL','ENGL','IRI2108-9','','')

insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '103092','WHITE','ENGL','ENGL','IRI2108-9','','')

insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '104654','WHITE','ENGL','ENGL','AMEAMERCN','','')

insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '1052','WHITE','ENGL','ENGL','AMEAMERCN','','')

insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '105307','WHITE','ENGL','ENGL','IRI2108-9','','')

insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '105307','WHITE','ENGL','ENGL','','AMEAMERCN','')

insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '105410','WHITE','ENGL','ENGL','AMEAMERCN','','')

insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '105410','WHITE','ENGL','ENGL','','IRI2108-9','')

insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '107139','WHITE','ENGL','ENGL','AMEAMERCN','','')

insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '107466','WHITE','ENGL','ENGL','AMEAMERCN','','')

insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '108584','WHITE','ENGL','ENGL','SPA2108-9','','')

insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '111168','WHITE','ENGL','ENGL','OTHOTHER','','')

insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '111168','WHITE','ENGL','ENGL','','','JEWISH')

insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '11173','WHITE','ENGL','ENGL','PORPORTUG','','')

insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '112026','WHITE','ENGL','ENGL','AMEAMERCN','','')

insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '112821','WHITE','ENGL','ENGL','AMEAMERCN','','')

insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '113118','WHITE','ENGL','ENGL','AMEAMERCN','','')

insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '114061','WHITE','ENGL','ENGL','AMEAMERCN','','')

insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '115492','WHITE','ENGL','ENGL','AMEAMERCN','','')

insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '11573','WHITE','ENGL','ENGL','AMEAMERCN','','')

insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '115744','WHITE','ENGL','ENGL','AMEAMERCN','','')

insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '11617','WHITE','ENGL','ENGL','ITA2108-9','','')

insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '11617','WHITE','ENGL','ENGL','','AMEAMERCN','')

insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '11617','WHITE','ENGL','ENGL','ITA2108-9','','')

insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '11617','WHITE','ENGL','ENGL','','AMEAMERCN','')

insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '116456','BLACK','ENGL','ENGL','AFR2058-6','','')

insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '116902','WHITE','ENGL','ENGL','AMEAMERCN','','')

insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '116927','WHITE','ENGL','ENGL','AMEAMERCN','','')

insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '117034','BLACK','ENGL','ENGL','AFR2058-6','','')

insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '117858','WHITE','ENGL','ENGL','AMEAMERCN','','')

insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '118817','WHITE','ENGL','ENGL','AMEAMERCN','','')

insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '119170','BLACK','ENGL','ENGL','AFR2058-6','','')

insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '119399','BLACK','ENGL','ENGL','AFR2058-6','','')

insert into TEST(Identifier, Race, PrimaryLanguage, PreferredLanguage, Ethnicity, Ethnicity2, Ethnicity3) values( '120155','WHITE','ENGL','ENGL','AMEAMERCN','','')

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

Something this is probably what you are looking for.

select Identifier, Race, PrimaryLanguage, PreferredLanguage, MAX(Ethnicity) as Ethnicity, MAX(Ethnicity2) as Ethnicity2, MAX(Ethnicity3) as Ethnicity3

from TEST

--where identifier = 100276

group by Identifier, Race, PrimaryLanguage, PreferredLanguage

Please understand there are some potential issues here. If you have the same identifier with multiple values for Race, PrimaryLanguage or PreferredLanguage you will get two rows. Also, if you have two rows for any given identifier and there is a value for more than 1 row in any of ethnicity columns you will get the one that sorts closest to the end of the alphabet. So for your example of 100276 if the first row Ethnicity = 'American' and the second row instead of being an empty string was 'German' it would return 'German' and NOT both values.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

NineIron SSChampion Points: 12537 More actions · Answer 4

Thanx. The data is such that there will be only one value for those fields.

Thanks again for your help.

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

You're welcome. Glad that will work for you.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/