CODEWITHSUNDEEP
sqlsql-server-2008
Patrick
Patrick

Reputation: 2577

Can I build a string using sql server case statements?

Is it possible to do something like this in SQL server 2008: 

CASE WHEN var1 = 1 THEN 'message 1 ' END as messages
CASE WHEN var2 = 1 THEN 'message 2 ' END as messages

Where messages would result in message 1 message 2 if both variables were true.

I do realize I can do a subquery and create the messages, but I was curious as to whether it could be done without a subquery or with statement.

Upvotes: 1

Views: 2401

Answers (2)

user170442
user170442

Reputation:

Yes you can, you have concatenate strings:

CONCAT(
  CASE WHEN var1 = 1 THEN 'message 1 ' END,
  CASE WHEN var2 = 1 THEN 'message 2 ' END
) as messages

Upvotes: 1

Amy B
Amy B

Reputation: 110111

Yes, you can do that by concatenating the strings and using an empty string when there is no content.

SELECT
  CASE WHEN @var1 = 1 THEN 'message 1 ' ELSE '' END +
  CASE WHEN @var2 = 1 THEN 'message 2 ' ELSE '' END as messages

Upvotes: 4

Related Questions