Case statement in select - 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 2000 Forums

Transact-SQL (2000)

Case statement in select

Author

Topic

brandonl
Yak Posting Veteran

58 Posts

Posted - 2002-08-23 : 09:55:41

I have the following query:

SELECT computername, manufacturer
FROM hardwaredata

Different machines report their serial numbers to different fields in another table (serialnumbers-fields: SN1, SN2, SN3).

What I want to do is something like this:

SELECT computername, manufacturer,
{if manufacturer = 'IBM'
(run sub query for SN1 field)
if manufacturer = 'HP'
(run sub query for SN2 field)
all else
(run sub query for SN3 field)}
FROM hardwaredata

Not sure how to proceed with this, I would like to use the query to make a view-so that output can be joined to another view. TIA!

~BrandonL

Teroman
Posting Yak Master

115 Posts

Posted - 2002-08-23 : 10:07:05

how about

select computername, manufacturer,

case
when manufacturer = 'IBM' then SN1
when manufacturer = 'HP' then SN2
else SN3
end as 'SerialNumber'

from hardwaredata

?

col

brandonl
Yak Posting Veteran

58 Posts

Posted - 2002-08-23 : 10:31:12

Thanks, that worked!

Hate it on Fridays when my brain gets stuck and can't figure out which direction to go

~BrandonL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources