Case Statment help - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Case Statment help

Author

Topic

vqcheese
Starting Member

19 Posts

Posted - 2009-06-19 : 14:04:28

My database has these 5 fields in a table called hr2nj01. How could i in a simple select statement do something like this:

hospitalizedcb_i
outpatient_i
emergencyroom_i
inhousetreatment_i
firstaid_i
---------------------
THis is what i want to do:
when hospitalizedcb_i = 1 then "Hospitalized
when outpatient_i = 1 then "Outpatient"
when emergencyroom_i = 1 then "ER"
when inhousetreatment_i = 1 then "Chiro"
when firstaid_i = 1 then "1st AID"
if none of those fields have a 1 then show NONE, i want this in a new column called Treatment.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-06-19 : 14:20:08

You've pretty much written already it

,case when hospitalizedcb_i = 1 then 'Hospitalized' end
,case when outpatient_i = 1 then 'Outpatient' end
,case when emergencyroom_i = 1 then 'ER' end
,case when inhousetreatment_i = 1 then 'Chiro' end
,case when firstaid_i = 1 then '1st AID' end

,[Treatment] = case when coalesce(hospitalizedcb_i,outpatient_i, etc.) is null then 'NONE' end

Jim

vqcheese
Starting Member

19 Posts

Posted - 2009-06-19 : 14:35:56

I just need 1 column Named Treatment, and when its one of those 5 things put that in that column. The above statment creates more 5 columns and one called treatment

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-19 : 14:59:14

I am not a wise guy but a database has no fields.
A table has fields (columns).
Your post is not clear enough to give us a chance to help.

quote:
hospitalizedcb_i
outpatient_i
emergencyroom_i
inhousetreatment_i
firstaid_i

What is that?
Is it: I have a table with one column and this column can have the above shown values
Is it: I have a table with 5 columns and above you can see the column names

Sorry but it is not clear what you have and what you need.

Greetings
Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

vqcheese
Starting Member

19 Posts

Posted - 2009-06-19 : 15:06:50

Sorry my Table has the 5 fields:
hospitalizedcb_i
outpatient_i
emergencyroom_i
inhousetreatment_i
firstaid_i
Each field is simply a 1 or a 0 in the table. I want a case statement that lookes at each of those fields and sees if its a 1 then it puts the word i noted above, if not it puts NONE, i wnat this all in a COLUMN called TREATMENT.

I can do this 1 field at a time, but i cant get it to combine all 5 fields

I hope this clears it up.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-06-19 : 15:11:26

Maybe you need a nested case like this...

select case when hospitalizedcb_i = 1 then 'Hospitalized'
else case when outpatient_i = 1 then 'Outpatient'
else case when emergencyroom_i = 1 then 'ER'
else case when inhousetreatment_i = 1 then 'Chiro'
else case when firstaid_i = 1 then '1st AID'
else 'NONE'
end
end
end
end
end as [Treatment]
from hr2nj01

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-06-19 : 15:13:46

I've never used the pivot or unpivot before, so this solution may be whacked. It was intersting to do though.
Jim

DECLARE @pvt TABLE (recid int,hospitalizedcb_i int, outpatient_i int, emergencyroom_i int
,inhousetreatment_i int,firstaid_i int
)

INSERT INTO @pvt VALUES (1,0,1,0,0,0)
INSERT INTO @pvt VALUES (2,0,1,1,1,1)
INSERT INTO @pvt VALUES (3,1,0,0,0,0)
INSERT INTO @pvt VALUES (4,0,0,0,0,0)
INSERT INTO @pvt VALUES (5,1,1,1,1,1)
--Unpivot the table.
SELECT recid,t1.Treatment,t1.Description
FROM
(SELECT * from @pvt) p
UNPIVOT
(bValue FOR Treatment IN
( hospitalizedcb_i , outpatient_i , emergencyroom_i
,inhousetreatment_i ,firstaid_i)
)AS unpvt
INNER JOIN
(select [Treatment] = 'hospitalizedcb_i', [Description] = 'Hospitalized'
union
select 'outpatient_i','OutPatient'
union
select 'emergencyroom_i','ER'
union
select 'inhousetreatment_i','Chiro'
union
select 'firstaid_i','1st Aid'
) t1
ON
unpvt.treatment = t1.treatment

Where unpvt.bvalue = 1

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-19 : 15:14:53

So if all 5 fields have the value 1.
Then how should be/become the value of treatment (all values concatenated or the last is winning) and is treatment a column in a table or just a column in the result set of the statement?

No, you're never too old to Yak'n'Roll if you're too young to die.

vqcheese
Starting Member

19 Posts

Posted - 2009-06-19 : 15:19:24

vijayisonly YOU R RIGHT ON THE MONEY THANKS.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-06-19 : 15:44:06

welcome

. From next time, you can provide sample data and expected output, it will help speed up the responses you receive.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-19 : 15:47:22

There is no need for nested statement.
The first true condition breaks the case.
See this:
select
case when 1 = 1 then 'A'
when 0 = 0 then 'B'
else 'C'
end

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources