CONCAT with IF condition in SQL Server

Question

I have a table with four columns presenting {YES, NO, N/A} values. What I'd like to obtain is a column with concatenated names of those columns which present a 'YES' value separate by a double underscore.

\, A, B, C, D
1, YES, NO, YES, N/A
2, NO, YES, N/A, N/A
3, YES, NO, NO, YES

Expected result:

A__C
B
A__D

Something like:

select CONCAT(
IF(A = 'YES', 'A'),
IF(B = 'YES', 'B'),
IF(C = 'YES', 'C'),
IF(D = 'YES', 'D'))
from my_table

Answer 1

Hope I understand you right, that you want a double underscore separator. This solution works without any subquery or cte processing.

select substring(
           iif(a='YES','__A','') + iif(b='YES','__B','') + 
           iif(c='YES','__C','') + iif(d='YES','__D','')
       ,3,100) 
from table1

One should know that this: substring('', 3, 100) will work using SqlServer.