Help with the Case statement - 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

Help with the Case statement

Author

Topic

rds207
Posting Yak Master

198 Posts

Posted - 2011-02-04 : 17:53:41

Hi

Need some help with the case statement ,

I have a column Procedure_name in table1 with the values below , i need to create a new column Sitename based on the procedure name , what is the best way to achive this i thought writing a case statement , please help..

ProcedureName Site
BaselineBuild-SD-POD1 Sandiego
BaselineBuild-SD-POD5 Sandiego
BaselineBuild-FRN1 Farnborough
BaselineBuild-SCL-POD1 Santaclara
BaselineBuild-SD Sandiego
BaselineBuild-SD-POD3 Sandiego
BaselineBuild-HYD-POD1

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-04 : 19:22:30

What would the desired result be from that sample data?

Poor planning on your part does not constitute an emergency on my part.

rds207
Posting Yak Master

198 Posts

Posted - 2011-02-04 : 19:30:25

I need to write a case statement to create a new column SiteName , depending on the procedurename values:

Here is the mapping :




ProcedureName            SiteName
BaselineBuild-SD-POD1   Sandiego
BaselineBuild-SD-POD5 	Sandiego
BaselineBuild-FRN1	Farnborough
BaselineBuild-SCL-POD1	Santaclara
BaselineBuild-SD	Sandiego
BaselineBuild-SD-POD3	Sandiego
BaselineBuild-HYD-POD1	Hyderabad

quote:
Originally posted by dataguru1971

What would the desired result be from that sample data?

Poor planning on your part does not constitute an emergency on my part.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-04 : 19:53:15

DECLARE @Proc table (ProcedureName varchar(50) not null)
INSERT INTO @Proc
SELECT 'BaselineBuild-SD-POD1' UNION -- Sandiego
SELECT 'BaselineBuild-SD-POD5' UNION -- Sandiego
SELECT 'BaselineBuild-FRN1' UNION --Farnborough
SELECT 'BaselineBuild-SCL-POD1' --Santaclara
SELECT 'BaselineBuild-SD' UNION --Sandiego
SELECT 'BaselineBuild-SD-POD3' UNION --Sandiego
SELECT 'BaselineBuild-HYD-POD1' --Hyderabad

SELECT
ProcedureName,
CASE LEFT(REPLACE(ProcedureName,'BaselineBuild-',''),2)
WHEN 'SD' THEN 'Sandiego'
WHEN 'FR' THEN 'Farnborough'
WHEN 'SC' THEN 'Santaclara'
WHEN 'HY' THEN 'Hyderabad'
END
FROM @proc t

Poor planning on your part does not constitute an emergency on my part.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-02-07 : 04:26:58

You should have a mapping table that has city names and join with the main table

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources