CODEWITHSUNDEEP
sqlsql-serversql-server-2016
Bruno
Bruno

Reputation: 4665

How to construct a new column based on other columns in a SELECT

I have this table:

Assets
--------------------------------
Description VARCHAR(50) NOT NULL
Suffix1 VARCHAR(50) NOT NULL
UseSuffix1 BIT NOT NULL
Suffix2 VARCHAR(50) NULL
UseSuffix2 BIT NOT NULL
Suffix3 VARCHAR(50) NULL
UseSuffix3 BIT NOT NULL

I am trying to do a SELECT statement that constructs the following: a VARCHAR(MAX) columns that consists of the Description field, plus the other suffixes appended when required (via the UseSuffixX flag)

examples of input and output :

'MyDesc'
'Suffix1'
0
NULL                 ->     'MyDesc'
0
NULL
0
-----------------------
'MyDesc'
'Suffix1'
1
NULL                 ->     'MyDesc - Suffix1'
0
NULL
0
-----------------------
'MyDesc'
'Suffix1'
0
'Suffix2'            ->     'MyDesc - Suffix2 - Suffix 3'
1
'Suffix3'
1
-----------------------
'MyDesc'
'Suffix1'
1
'Suffix2'            ->     'MyDesc - Suffix1 - Suffix 3'
0
'Suffix3'
1

I started by using a CASE directive in my SELECT like this:

SELECT
    [Description] + 
    CASE 
        WHEN UseSuffix1 = 1 THEN ' - ' + Suffix1
        WHEN UseSuffix2 = 1 THEN ' - ' + Suffix2
        WHEN UseSuffix3 = 1 THEN ' - ' + Suffix3
        ELSE ''
    END
FROM Assets

but quickly realized that I would need to expand the trees of all possibilities in each WHEN branch...not sure if I'm expressing myself correctly here.

What would be the more practical way to do this?

Upvotes: 0

Views: 66

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270873

You don't need all the possibilities, just one case per suffix:

SELECT ([Description] + 
        (CASE WHEN UseSuffix1 = 1 THEN ' - ' + Suffix1 ELSE '' END) +
        (CASE WHEN UseSuffix2 = 1 THEN ' - ' + Suffix2 ELSE '' END) +
        (CASE WHEN UseSuffix3 = 1 THEN ' - ' + Suffix3 ELSE '' END) 
       )
FROM Assets

Upvotes: 2

Related Questions