SQL server – Update one column based on the values of other columns

Say there are five columns in the table and I’d like to update the column E based on the value of column A,B,C,D. If the corresponding value is Y, then write the column name as part of the value in column E. The expected result is shown in the second image.

Originally, the data in the table looks like this

Expected result:

Answer

If you number of columns is fixed, then you can use a CASE statement.

Sample data

create table data
(
  A nvarchar(1),
  B nvarchar(1),
  C nvarchar(1),
  D nvarchar(1),
  E nvarchar(10)
);

insert into data (A, B, C, D) values
('Y', 'N', 'Y', 'Y'),
('N', 'N', 'N', 'Y'),
('N', 'Y', 'Y', 'N'),
('Y', 'Y', 'Y', 'N');

Solution

update d
set d.E = substring(
          case d.A when 'Y' then ',A' else '' end
        + case d.B when 'Y' then ',B' else '' end
        + case d.C when 'Y' then ',C' else '' end
        + case d.D when 'Y' then ',D' else '' end,
         2, 100)
from data d;

Result

select * from data;

A   B   C   D   E
--- --- --- --- -------
Y   N   Y   Y   A,C,D
N   N   N   Y   D
N   Y   Y   N   B,C
Y   Y   Y   N   A,B,C

SQL Fiddle