Printing out list of attribute descriptions based on boolean columns in SQL

I've got a table with a few boolean columns like: IsProductionWorker, IsMaterialHandler, IsShopSupervisor, etc. A record in this table may have several of these column values as true.

What I would like to do is have a query that returns 1 field with a list of all the attributes that are true, say AttributeList which, if those 3 columns were true would return: "Production Worker, Material Handler, Shop Supervisor".

I can think of a few brute force methods and maybe a more el开发者_如何学Cegant method through the use of a temp-table (I've done similar things before), but I'm curious as to what the best way of doing this would be and if there are any easier implementations.

Thanks.

---

No elegance is possible, really. Simplicity, yes.

Per row you want to change a flag into a string, for each flag. Not many options for cleverness...

SELECT
    SUBSTRING (
       CASE WHEN IsProductionWorker = 1 THEN ', Production Worker' ELSE '' END +
       CASE WHEN IsMaterialHandler= 1 THEN ', Material Handler' ELSE '' END +
       CASE WHEN IsShopSupervisor= 1 THEN ', Shop Supervisor' ELSE '' END +
       ... ,
       3, 8000)
FROM
    MyTable
WHERE
     ...

---

You can try this.

select CASE WHEN isProductionWorker = 1 THEN 'Production Worker' ELSE '' END
    + CASE WHEN cast(isProductionWorker as int) + isMaterialHandler = 2 THEN ', ' else '' END
    + CASE WHEN isMaterialHandler = 1 THEN 'Material Handler' ELSE '' END
    + CASE WHEN cast(isProductionWorker as int) + isMaterialHandler + isShopSupervisor > 1 THEN ', ' else '' END
    + CASE WHEn isShopSupervisor = 1 THEN 'Shop Supervisor' ELSE '' END AS AttributeList
from MyTable