CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012
Michael A
Michael A

Reputation: 9900

Creating a comma delimited list of checked bit values

I have a table similar to the following:

CREATE TABLE [dbo].[TestTable] (
    [Id]                        INT        NOT NULL,
    [FoodCard]                  BIT             NULL,
    [FuelCard]                  BIT             NULL,
    [GoCard]                    BIT             NULL,
    [Created]                   DATETIME        NOT NULL,
    [CreatedBY]                 VARCHAR (150)   NOT NULL,
    [Modified]                  DATETIME        NULL,
    [ModifiedBy]                VARCHAR (150)   NULL,
    [PaymentTotal]              DECIMAL (10, 2) NULL
);

Using a stored procedure I need to create a comma delimited list of the bit values that are true (so if FuelCard was 1 my result would be Fuel Card but if FuelCard and FoodCard were one my result would be Fuel Card, Food Card. In my actual table I have about 12 bit values to work with.

I've thought about doing with with a long series of case statements to create my comma delimited list but it seems very bad practice to do so. What would be the better way of tackling the problem?

Upvotes: 0

Views: 53

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269943

You can do this with case like this:

select stuff( (case when FoodCard = 1 then ', FoodCard' else '' end) +
              (case when FuelCard = 1 then ', FuelCard' else '' end) +
              (case when GoCard = 1 then ', GoCard' else '' end) +
              . . .,
              1, 2, '') as cardList

The stuff() removes the first two characters, which as ', '.

Upvotes: 2

Related Questions