SELECT task - 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)

SELECT task

Author

Topic

agniwoni
Starting Member

28 Posts

Posted - 2007-07-06 : 10:32:31

i need to do a select on TABLE1(COLUMN1, COLUMN2.... etc):

Select COLUMN1, Expr from TABLE1

Expr should be:
'1' when COLUMN1 is in (a, b, c)
'2' when COLUMN1 is in (d, e, f)
COLUMN2 when COLUMN1 is not in (a, b, c, d, e, f)

How to prepare a proper sql select statement?
Thanks in advance for any help!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-06 : 10:35:09

[code]SELECT COLUMN1,
Expr = CASE WHEN COLUMN1 IN ('a', 'b', 'c') THEN '1'
WHEN COLUMN1 IN ('d', 'e', 'f') THEN '2'
WHEN COLUMN1 NOT IN ('a', 'b', 'c', 'd', 'e', 'f') THEN COLUMN2
END
FROM TABLE1[/code]

KH
[spoiler]Time is always against us[/spoiler]

agniwoni
Starting Member

28 Posts

Posted - 2007-07-06 : 11:03:09

quote:

Originally posted by khtan

SELECT COLUMN1,
       Expr = CASE WHEN COLUMN1 IN ('a', 'b', 'c') THEN '1'
                   WHEN COLUMN1 IN ('d', 'e', 'f') THEN '2'
                   WHEN COLUMN1 NOT IN ('a', 'b', 'c', 'd', 'e', 'f') THEN COLUMN2
                   END
FROM   TABLE1

KH
[spoiler]Time is always against us[/spoiler]

Thanks! It works gr8!

Regards!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-06 : 11:05:02

Simpler

SELECT COLUMN1,
       Expr = CASE WHEN COLUMN1 IN ('a', 'b', 'c') THEN '1'
                   WHEN COLUMN1 IN ('d', 'e', 'f') THEN '2'
                   ELSE COLUMN2
                   END
FROM   TABLE1

Peter Larsson
Helsingborg, Sweden

agniwoni
Starting Member

28 Posts

Posted - 2007-07-06 : 11:32:29

quote:

Originally posted by Peso

Simpler

SELECT COLUMN1,
       Expr = CASE WHEN COLUMN1 IN ('a', 'b', 'c') THEN '1'
                   WHEN COLUMN1 IN ('d', 'e', 'f') THEN '2'
                   ELSE COLUMN2
                   END
FROM   TABLE1

Peter Larsson
Helsingborg, Sweden

Figured it out ;-)
Thx/regards

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources