I'd like to be able to select the field name from a table if a boolean condition is met. I've got a users table which has a userID field, plus a series of 1/0 tinyint fields to control the display of certain dashboard items. If the field is set to 1, I'd like this query to return the field name.
The stored procedure that I have so far is:
SELECT userShowProcess, userShowLEN, userShowContractExp, userShowWO, userShowTSO, userShowOcc, userShowRev, userShowDelinq, userShowExp
WHERE userID = @UserID;
That gives me 9 columns with each set to a 1 or 0. While this works at the moment, my problem arises if I need to add options to the system -- there would be quite a bit of rewriting on the code side. Plus, I currently have to iterate the recordset (which I've saved into an array) to weed out the zeroes. I've looked into syscolumns and sysobjects but I really don't know what I'm looking at when I see examples of these. My ideal query would show something like this, assuming the user has "1" in four of the 9 fields: