3 columns in table, 1 in Select statement output

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)

3 columns in table, 1 in Select statement output

Author

Topic

francism
Starting Member

22 Posts

Posted - 2008-07-08 : 11:23:31

Hello,

This is my first forum posting, so please be gentle!

I have a table with three columns, subject1, subject2 and subject3. For each row, only one of the three columns has valid data - the valid column has a value greater than 1.

I'd like to write a select statement that has 1 column as output - the valid column. I can't see how make the CASE statement work.

In other languages you can do something like this:


if subject1 > 1 then subject = subject1 else
if subject2 > 1 then subject = subject2 else subject = subject3;

How can you do something like this in a Select statement?

Thank you very much.

Francis.

Microsoft CRM 3 - SQL Server 2000

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-08 : 11:25:37

yes

SELECT CASE WHEN subject1 > 1 THEN subject1
            WHEN subject2 > 1 THEN subject2
            ELSE subject3
            END  AS subject
FROM   yourtable

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

francism
Starting Member

22 Posts

Posted - 2008-07-08 : 11:36:23

Thank you khtan!

I couldn't figure out how to code the second clause of the CASE statement.

Regards,

Francis.

Microsoft CRM 3 - SQL Server 2000

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources