change matches on left joins to "1"

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

change matches on left joins to "1"

Author

Topic

brucezepplin
Starting Member

15 Posts

Posted - 2011-07-22 : 05:06:29

Hi, I have the following problem for a medical research question. Imagine having a list of people and you ask them 2 quesions - do you smoke? do you have diabetes? etc etc. In sql I have made a series of left joins so that a table has been made where if they have answered yes to a question, the word " SMOKER " comes up in the column field smoker. And the same for any other positive values. I want to update the word "SMOKER" to "YES" as smoker is already the column header.

I have this:

Smoker Diabetes
---------------------------------------------------------
Person1 smoker
Person2 diabetes

but what I want is this:

Smoker Diabetes
---------------------------------------------------------
Person1 Yes
Person2 Yes

My code is:


SELECT DISTINCT Person, question.s AS Smoker, q.d AS Diabetic from
(SELECT DISTINCT Person, question
FROM database
WHERE answer = 'smoker') A

LEFT JOIN

(SELECT DISTINCT Person, question
FROM database
WHERE answer = 'diabetes') B

ON A.Person = B.Person

Thanks in advance!

brucezepplin
Starting Member

15 Posts

Posted - 2011-07-22 : 05:09:50

ok the format messed up a bit.

What I have is:

...........Smoker........Diabetic
----------------------------------
Person1....smoker........
Person2..................diabetic

But what I want is:

...........Smoker........Diabetic
----------------------------------
Person1.....yes........
Person2.....................yes

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-07-22 : 05:18:17

SELECT Person, CASE WHEN Smoker > '' THEN 'Yes' ELSE '' END AS Smoker, CASE WHEN Diabetic > '' THEN 'Yes' ELSE '' END AS Diabetic
FROM dbo.Table1

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources