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