So I'm trying to do several things at once and I'm not sure how to put them together.
My first step was to take multiple cells and combine them into 1 row based on id.
USE MAIN;
Select ACT.ID AS Activity
,STUFF (( Select ',' + LE.Name AS [text()]
FROM ActivityDemographicEthnicity ADE
LEFT OUTER JOIN LookupEthnicity LE
ON LE.ID = ADE.ethinicityId
WHERE ACT.ID = ADE.ActivityID
FOR XML PATH ('')),1,1,'')
AS Ethnicity
FROM Activity ACT
This works well and produces something like this:
ID Ethnicity
------------------------------------------
123 African, African American, Caucasian
125 Caucasian
128 NULL
BUT I would like something like this:
ID Ethnicity
----------------------------------------
123 African American, Caucasian
125 Caucasian
128 General Market
Where I've defined new values via a case statement:
USE Main;
(Select CASE
When LE.Name is null THEN 'General Market'
When LE.Name = 'African' THEN 'African American'
When LE.Name = 'American Indian/Native American' Then 'American Indian'
WHEN LE.NAME = 'Cambodian' Then 'Asian'
When LE.Name = 'Chinese' Then 'Asian'
When LE.Name = 'Filipino' Then 'Asian'
When LE.Name = 'Hmong' Then 'Asian'
WHEN LE.NAME = 'Japanese' THEN 'Asian'
WHEN LE.Name = 'Korean' Then 'Asian'
WHEN LE.Name = 'Laotian' Then 'Asian'
WHEN LE.Name = 'Vietnamese' Then 'Asian'
WHEN LE.Name = 'Armenian' Then 'Other'
WHEN LE.Name = 'Russian' Then 'Other'
WHEN LE.Name = 'Ukranian' Then 'Other'
WHEN LE.Name = 'Other (specify)' Then 'Other'
Else LE.Name
End 'Ethnicity'
From ActivityDemographicEthnicity ADE
LEFT JOIN LookupEthnicity LE
ON LE.ID = ADE.ID)`
Both queries work individually but how would I combine them into one query?
Give this a whirl:
Select ACT.ID AS Activity,
ISNULL(STUFF((Select distinct ',' + CASE
When LE.Name is null THEN 'General Market'
When LE.Name = 'African' THEN 'African American'
When LE.Name = 'American Indian/Native American' Then 'American Indian'
WHEN LE.NAME = 'Cambodian' Then 'Asian'
When LE.Name = 'Chinese' Then 'Asian'
When LE.Name = 'Filipino' Then 'Asian'
When LE.Name = 'Hmong' Then 'Asian'
WHEN LE.NAME = 'Japanese' THEN 'Asian'
WHEN LE.Name = 'Korean' Then 'Asian'
WHEN LE.Name = 'Laotian' Then 'Asian'
WHEN LE.Name = 'Vietnamese' Then 'Asian'
WHEN LE.Name = 'Armenian' Then 'Other'
WHEN LE.Name = 'Russian' Then 'Other'
WHEN LE.Name = 'Ukranian' Then 'Other'
WHEN LE.Name = 'Other (specify)' Then 'Other'
ELSE LE.Name
END
FROM ActivityDemographicEthnicity ADE
LEFT OUTER JOIN LookupEthnicity LE ON LE.ID = ADE.ethnicityId
WHERE ACT.ID = ADE.ActivityID
FOR XML PATH ('')),1,1,''), 'General Market') AS Ethnicity
FROM Activity ACT
You need to wrap the whole STUFF function in an ISNULL to catch the cases where an Activity doesn't have a corresponding ActivityDemographicEthnicity. And select distinct inside the STUFF to remove duplicates.