ibiza ibiza - 1 month ago 4
SQL Question

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?

Answer

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