Defining column descriptions - 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

Defining column descriptions

Author

Topic

srattman72
Starting Member

4 Posts

Posted - 2011-08-09 : 11:20:33

If I have a dataset that looks similar to this:

ID Criteria1 Criteria2 Criteria3 Criteria4
003 1 0 0 0
801 0 1 0 0
001 0 0 1 0
502 0 0 0 1

How do I convert these 4 criteria to be represented as one variable? For example, if Criteria1 = 1 then FinCrit = 'C1', else if Criteri2 = 1, then FinCrit = 'C2', etc.

Second part to question -- what if more than 1 criteria has a value of 1 and I have to prioritize which Criteria to define (let's assume that Criteria1 is highest priority, then Criteria2, etc.)?

Thanks

Shane

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-08-09 : 11:44:11

SELECT FinCrit = CASE
WHEN Criteria1=1 THEN 'C1'
WHEN Criteria2=1 THEN 'C2'
WHEN Criteria3=1 THEN 'C3'
WHEN Criteria4=1 THEN 'C4'
ELSE 'C0' END

List the WHEN conditions in order of priority, the first CASE that evaluates as true will be returned, the rest are ignored.

srattman72
Starting Member

4 Posts

Posted - 2011-08-09 : 12:49:33

Rob -

Thanks for the info -- just one clarification question:

Can I just use the 'C4' as my ELSE statement instead of the 'C0'? All records will have at least one of the criteria, so there will be no records where all 4 are zeroes.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-08-09 : 13:08:25

Sure, you can do that.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources