Case
-
hey guys, i have a Tsql using case with the following output
ID apple banana berry cherry
1 y null null nulll
1 null y null null
2 null null y null
2 y null null null
But what i want is the following
ID apple banana berry cherry
1 y y null nulll
2 y nully y null
thx for ur help fellas
-
This exact same problem has appeared a couple of times now... starting to look a lot like homework... please post the code you've tried and then we'll help you figure it out.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Select distinct Patient_Demographics.KPAIDS_NO, Patient_Demographics.LastName,Patient_Demographics.FirstName
,
Case when (dbo.CDC_Clinical_Diagnosis_lookup.Clinical_Diagnosis like 'Lymphadenopathy %' or dbo.CDC_Clinical_Diagnosis_lookup.Clinical_Diagnosis = 'Lymphadenopathy' ) then 'Yes' end as 'Lymphadenopathy',
Case when (dbo.CDC_Clinical_Diagnosis_lookup.Clinical_Diagnosis like 'Candidiasis %' or dbo.CDC_Clinical_Diagnosis_lookup.Clinical_Diagnosis = 'Candidiasis') then 'Yes' end as 'Candidiasis',
Case when (dbo.CDC_Clinical_Diagnosis_lookup.Clinical_Diagnosis like 'Toxoplasmosis %' or dbo.CDC_Clinical_Diagnosis_lookup.Clinical_Diagnosis = 'Toxoplasmosis') then 'Yes' end as 'Toxoplasmosis',
Case when (dbo.CDC_Clinical_Diagnosis_lookup.Clinical_Diagnosis like 'Hepatomegaly %' or dbo.CDC_Clinical_Diagnosis_lookup.Clinical_Diagnosis = 'Hepatomegaly') then 'Yes' end as 'Hepatomegaly',
Case when (dbo.CDC_Clinical_Diagnosis_lookup.Clinical_Diagnosis like 'Splenomegaly %' or dbo.CDC_Clinical_Diagnosis_lookup.Clinical_Diagnosis = 'Splenomegaly') then 'Yes' end as 'Splenomegaly'
--Case when dbo.CDC_Clinical_Diagnosis_lookup.Clinical_Diagnosis in('Eczema','Eczema %') then 'Yes' end as 'Toxoplasmosis'
from Patient_Demographics
inner join
Patient_CDC_Clinical_Diagnosis
on
Patient_Demographics.KPAIDS_NO = Patient_CDC_Clinical_Diagnosis.KPAIDS_NO
inner join
dbo.CDC_Clinical_Diagnosis_lookup
on
Patient_CDC_Clinical_Diagnosis.CDC_Diagnosis_Code = dbo.CDC_Clinical_Diagnosis_lookup.Diagnosis_Code
where dbo.Patient_Demographics.Mother_Or_Child ='1'
and
(
Case when (dbo.CDC_Clinical_Diagnosis_lookup.Clinical_Diagnosis like 'Lymphadenopathy %' or dbo.CDC_Clinical_Diagnosis_lookup.Clinical_Diagnosis = 'Lymphadenopathy' ) then 'Yes' end is not null or
Case when (dbo.CDC_Clinical_Diagnosis_lookup.Clinical_Diagnosis like 'Candidiasis %' or dbo.CDC_Clinical_Diagnosis_lookup.Clinical_Diagnosis = 'Candidiasis' ) then 'Yes' end is not null or
Case when (dbo.CDC_Clinical_Diagnosis_lookup.Clinical_Diagnosis like 'Toxoplasmosis %' or dbo.CDC_Clinical_Diagnosis_lookup.Clinical_Diagnosis = 'Toxoplasmosis') then 'Yes' end is not null or
Case when (dbo.CDC_Clinical_Diagnosis_lookup.Clinical_Diagnosis like 'Hepatomegaly %' or dbo.CDC_Clinical_Diagnosis_lookup.Clinical_Diagnosis = 'Hepatomegaly') then 'Yes' end is not null or
Case when (dbo.CDC_Clinical_Diagnosis_lookup.Clinical_Diagnosis like 'Splenomegaly %' or dbo.CDC_Clinical_Diagnosis_lookup.Clinical_Diagnosis = 'Splenomegaly') then 'Yes' end is not null
--Case when dbo.CDC_Clinical_Diagnosis_lookup.Clinical_Diagnosis in('Eczema','Eczema %') then 'Yes' end is not null
)
go
-
SELECT ID, MAX(apple), MAX(banana), MAX(berry), MAX(cherry)
FROM Table
GROUP BY ID
ORDER BY ID
-
Or, in your terms, Denby...
SELECT pd.KPAIDS_NO, pd.LastName,pd.FirstName,
MAX(CASE WHEN dl.Clinical_Diagnosis LIKE 'Lymphadenopathy%' THEN 'Yes' END) AS Lymphadenopathy,
MAX(CASE WHEN dl.Clinical_Diagnosis LIKE 'Candidiasis%' THEN 'Yes' END) AS Candidiasis,
MAX(CASE WHEN dl.Clinical_Diagnosis LIKE 'Toxoplasmosis%' THEN 'Yes' END) AS Toxoplasmosis,
MAX(CASE WHEN dl.Clinical_Diagnosis LIKE 'Hepatomegaly%' THEN 'Yes' END) AS Hepatomegaly,
MAX(CASE WHEN dl.Clinical_Diagnosis LIKE 'Splenomegaly%' THEN 'Yes' END) AS Splenomegaly
FROM dbo.Patient_Demographics pd
INNER JOIN dbo.Patient_CDC_Clinical_Diagnosis cd
ON pd.KPAIDS_NO = cd.KPAIDS_NO
INNER JOIN dbo.CDC_Clinical_Diagnosis_lookup dl
ON cd.CDC_Diagnosis_Code = dl.Diagnosis_Code
WHERE pd.Mother_Or_Child = '1'
AND (
dl.Clinical_Diagnosis LIKE 'Lymphadenopathy%'
OR dl.Clinical_Diagnosis LIKE 'Candidiasis%'
OR dl.Clinical_Diagnosis LIKE 'Toxoplasmosis%'
OR dl.Clinical_Diagnosis LIKE 'Hepatomegaly%'
OR dl.Clinical_Diagnosis LIKE 'Splenomegaly%'
)
GROUP BY pd.KPAIDS_NO, pd.LastName, pd.FirstName
Obviously, I don't have your data tables so I can't test it properly
... but the read get's much simpler if you apply table aliases and understand that "NULLS HAPPEN" which greatly simplifies the WHERE clause and most of the SELECT list.Like I said, I could have an error somewhere in the code above... but it should be pretty close.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
worked like a charm, thx guys.......but could u explain to me why use "max" and the problem as to why i was getting the initial results.
thx again fellas
-
Sure... it's definitely a trick... we needed some way to aggragate all of the "findings" to a single row for each ID/name. We also needed the some aggragate to wrap the CASE statements in an aggragate so the GROUP BY would ignore them when it came to what to group on (ID/name only)... MAX happens to fit that bill without reshaping the underlying data (like a COUNT would). Doesn't work real well if you have more than 2 possibilites (it's there or it's null) for things like this... (would work fine for NULL, No, and Yes, though, because Yes is alphabetically superior to No).
Also notice that you had things like SomeCol LIKE 'A %' OR SomeCol = 'A' and that just wasn't needed because of the way LIKE works... changed to to just SomeCol LIKE 'A%' and the '%' can be zero or more characters which will also find SomeCol = 'A'.
Sorry about the homework accusation... most of us have no problem helping others do homework but we want students (and others) to learn something along the way. The prerequisite (at least in my simple mind) for that kind of help is "show me that you actually tried" and aren't just looking for a handout to make a grade or being lazy.
And, although I certainly appreciate the wonderful way you simplified the problem definition (wish my Systems Analysts could do that
), now you see why I always ask for the "real" code... I figure if you're asking a question, there may be other things I can do to help on the code. 
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
thx for explaining jeff.......
ooh and abt the homework thing i totally understand what your saying....Like the saying "God help those who help themselves"
so i guess ur a SQL GOD
-
Heh... thanks for the nice compliment, but I'm just another Homer like everyone else... sometimes I can make things work
... sometimes not so good 
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply