To CASE or Not to CASE - 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

General SQL Server Forums

New to SQL Server Programming

To CASE or Not to CASE

Author

Topic

tictoc
Starting Member

7 Posts

Posted - 2011-02-28 : 20:00:27

Having a hard time trying to figure out how to do this
I have 4 columns of data
Os column one Os column two Os column Three Os column Four
In each I can have across all of them things like vista, xp , win 7, win 2000
Vista might be in one four or three no patterns that I can see.
So when I do a select I get
OS ID| Os column one| Os column two| Os column Three |Os column Four

1 VISTA XP WIN 7 WIN 200

2 Win 7 win 2000 xp vista

3 win 2000 vista xp win 7

I am trying to get the data to return like this
OS ID| VISTA| XP| WIN 7 | WIN 2000

1 VISTA XP WIN 7 WIN 2000

2 vista xp Win 7 win 2000

3 vista xp Win 7 win 2000
I figured I could use a CASE statement and return the search to my created columns
But I cannot get my CASE query to search all of the columns only one.
Any ideas?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-01 : 07:24:07

[code]select
[OS ID],
case when
[Os column one] = 'VISTA' or
[Os column two] = 'VISTA' or
[Os column Three] = 'VISTA' or
[Os column Four] = 'VISTA' then 'VISTA'
else '' end as [VISTA],
-- repeat the above case statement with VISTA replaced by XP.
-- repeat the above case statement with VISTA replaced by WIN 7.
-- repeat the above case statement with VISTA replaced by WIN 2000.
from
YourTable[/code]

tictoc
Starting Member

7 Posts

Posted - 2011-03-01 : 08:55:23

Thank you! Much appreciated...

quote:

Originally posted by sunitabeck

select
   [OS ID],
   case when
      [Os column one] = 'VISTA' or
      [Os column two] = 'VISTA' or
      [Os column Three] = 'VISTA' or
      [Os column Four] = 'VISTA' then 'VISTA'
   else '' end as [VISTA],
   -- repeat the above case statement with VISTA replaced by XP.
   -- repeat the above case statement with VISTA replaced by WIN 7.
   -- repeat the above case statement with VISTA replaced by WIN 2000.
from
   YourTable

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources