Multiple Selections - 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

Multiple Selections

Author

Topic

pgmr1998
Yak Posting Veteran

66 Posts

Posted - 2012-05-31 : 17:21:40

How can I place multiple selection values into a field using a case statement. The statement checks a group of related check boxes and sets the value based upon the check boxes being set to -1. However, more than one check box may have been selected. For example, the "LAAC" and the "AG" check boxes may both have been checked.
Here is the code:

Select
CASE
WHEN LAAC = -1 THEN 'LAAC'
WHEN AG = -1 THEN 'AG'
WHEN DA = -1 THEN 'DA'
WHEN FRC = -1 THEN 'FRC'
WHEN ETHICS = -1 THEN 'ETHICS'
WHEN Other = -1 THEN OtherReason
ELSE ' '
END as 'FurtherActionReq', controlnumber
from lla_audits
order by controlnumber

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-05-31 : 17:35:39

If they're both checked, which one takes precedence? What should be returned if they're both checked?

pgmr1998
Yak Posting Veteran

66 Posts

Posted - 2012-05-31 : 17:37:03

If both are checked, then I want both values returned, separated by a space.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-05-31 : 17:44:45

SELECT LTRIM(CASE WHEN LAAC = -1 THEN 'LAAC' ELSE '' END +
CASE WHEN AG = -1 THEN ' AG' ELSE '' END +
CASE WHEN DA = -1 THEN ' DA' ELSE '' END +
CASE WHEN FRC = -1 THEN ' FRC' ELSE '' END +
CASE WHEN ETHICS = -1 THEN ' ETHICS' ELSE '' END +
CASE WHEN Other = -1 THEN ' ' + OtherReason END) AS 'FurtherActionReq', controlnumber
FROM lla_audits
ORDER BY controlnumber

edit: fixed expression for OtherReason CASE.

pgmr1998
Yak Posting Veteran

66 Posts

Posted - 2012-05-31 : 17:55:54

Perfect! Thanks...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources