How to update column with concatenation of other column values

user_13386139 0
Viewed 130

I have a table with 4 columns: Science, Math, English, and Classes. Science, Math, and English are booleans, and Classes is nvarchar.

If Science is 1, then Classes should get appended with a semicolon and the string 001. If Math is 1, then Classes should get appended with a semicolon and the string 002. If English is 1, then Classes should get appended with a semicolon and the string 003.

So if Science was 1, Math was 0, and English was one, then Classes would have 001;003. Or if Science was 0, Math was 1, and English was zero, then Classes would have 003.

I initially tried:

Update Table
SET Classes = CASE
                WHEN Science = 1 THEN concat(Classes, ';001')
                WHEN Math = 1 THEN concat(Classes, ';002')
                WHEN English = 1 THEN concat(Classes, ';003')
              END

But this won't work because as soon as the CASE finds a true statement, it updates that and won't check the other conditions. Can anyone help with figuring out how to do this kind of concatenation? Thanks!

concatenation sql-update sql-server sql
3 Answers

For SQL Server 2017 onwards.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, Science BIT, Math BIT, English BIT, Classes VARCHAR(20) DEFAULT (NULL));
INSERT INTO @tbl (Science, Math, English) VALUES
(1, 0, 1),
(0, 1, 0);
-- DDL and sample data population, end

-- before
SELECT * FROM @tbl;

UPDATE @tbl
SET Classes = CONCAT_WS(';', IIF(Science=1, '001',NULL), IIF(Math=1, '002',NULL), IIF(English=1, '003',NULL))

-- after
SELECT * FROM @tbl;

Output

+----+---------+------+---------+---------+
| ID | Science | Math | English | Classes |
+----+---------+------+---------+---------+
|  1 |       1 |    0 |       1 | 001;003 |
|  2 |       0 |    1 |       0 | 002     |
+----+---------+------+---------+---------+
user_19323110

You need 3 CASE expressions inside CONCAT():

UPDATE Table
SET Classes = CONCAT(Classes, 
                CASE WHEN Science = 1 THEN ';001' END,
                CASE WHEN Math = 1 THEN ';002' END,
                CASE WHEN English = 1 THEN ';003' END
              )
WHERE 1 IN (Science, Math, English);
user_104988280

Another option is to use the slightly more compact iif

classes=
concat(
    iif(science=1,';001',''),
    iif(math=1,';002',''),
    iif(english=1,';003','')
)

Because in your question you indicate you don't want the first semicolon you can remove it with stuff

stuff(concat(
    iif(science=1,';001',''),
    iif(math=1,';002',''),
    iif(english=1,';003','')
),1,1,'')
user_153326500
Related