CODEWITHSUNDEEP
sqlsql-server-2008
devlife
devlife

Reputation: 16145

Convert table to comma delimited string

I have a single row in a table variable that looks like this:

CanEdit | CanView | CanAdd | CanDelete

All columns are bit fields. I need to convert this table to a comma-delimited string like the following:

How can I do this?

Upvotes: 1

Views: 554

Answers (1)

MatBailie
MatBailie

Reputation: 86706

As it's a fixed set, it can be done directly as follows.

SUBSTRING(
    CASE WHEN CanEdit   = 1 THEN ',E' ELSE '' END
  + CASE WHEN CanView   = 1 THEN ',V' ELSE '' END
  + CASE WHEN CanAdd    = 1 THEN ',A' ELSE '' END
  + CASE WHEN CanDelete = 1 THEN ',D' ELSE '' END,
  2,
  7
)

That said, it is very rare that this is recommended.

If you are refactoring your data, this is reversing the atomicity of your data and is considered a fundamentally problematic anti-pattern in SQL.

If you are doing this for presentation in a client, doing this in SQL couples your data layer and presentation layer, another anti-pattern.

Upvotes: 3

Related Questions