Create a custom column based on the values of three other columns

Question

In this project I'm currently working on (I'm building a bridge between a desktop app and a new e-shop) there is a products table that has some spare columns defined that can be used for whatever reason the end user might need some custom data to be stored into.

So, the user needed to set a true/false flag to determine whether the products would appear in three different sliders... Unfortunately, the person who implemented this, didn't even use the same type of spare columns... So,

• Slider1's flag is stored in a varchar(50) column
• Slider2's flag is stored in a float column
• Slider3's flag is stored in a float column

Additionally I ran a SELECT DISTINCT <column> for each one of them to get an idea of the actual data stored in each column and got the following results:

• The varchar column has the following data stored in it:

FLDSTRING1
NULL
''
0
1
194276400456

• The float column has the following data stored:

FLDFLOAT5
NULL
0
1

• And the other float column has this:

FLDFLOAT6
NULL
1

Also, I ran the following query to find the different combinations of the data stored for each column:

SELECT FLDSTRING1, FLDFLOAT5, FLDFLOAT6
FROM MATERIAL
GROUP BY FLDSTRING1, FLDFLOAT5, FLDFLOAT6

and got the following combinations...

FLDSTRING1	FLDFLOAT5	FLDFLOAT6
NULL	NULL	NULL
NULL	NULL	1
NULL	0	NULL
NULL	1	NULL
NULL	1	1
''	NULL	NULL
''	NULL	1
0	NULL	NULL
0	0	NULL
1	NULL	NULL
1	NULL	1
1	0	NULL
1	1	NULL
1	1	1
194276400456	0	NULL

What I need after all this introduction...

I want a concatenated string of three comma-separated values like this

• NEWPROD for when FLDSTRING1 would evaluate to true - anything not NULL, 0, or ''
• CUSTOM1 for when FLDFLOAT5 would evaluate to true - basically the value 1
• CUSTOM2 for when FLDFLOAT6 would evaluate to true - again value 1

After some trial and error I managed to bring this to a point that it kind of works, in the sense that it brings the correct values, not comma-separated though...

SELECT 
    FLDSTRING1, FLDFLOAT5, FLDFLOAT6, 
    CONCAT(CASE WHEN ISNULL(FLDSTRING1, '') = '' THEN '' ELSE 'NEWPROD' END, 
           CASE WHEN ISNULL(FLDFLOAT5,  '') = '' THEN '' ELSE 'CUSTOM1' END,
           CASE WHEN ISNULL(FLDFLOAT6,  '') = '' THEN '' ELSE 'CUSTOM2' END) AS TAGS
FROM 
    MATERIAL
GROUP BY 
    FLDSTRING1, FLDFLOAT5, FLDFLOAT6;

FLDSTRING1	FLDFLOAT5	FLDFLOAT6	TAGS
NULL	NULL	NULL
NULL	NULL	1	CUSTOM2
NULL	0	NULL
NULL	1	NULL	CUSTOM1
NULL	1	1	CUSTOM1CUSTOM2
''	NULL	NULL
''	NULL	1	CUSTOM2
0	NULL	NULL	NEWPROD
0	0	NULL	NEWPROD
1	NULL	NULL	NEWPROD
1	NULL	1	NEWPRODCUSTOM2
1	0	NULL	NEWPROD
1	1	NULL	NEWPRODCUSTOM1
1	1	1	NEWPRODCUSTOM1CUSTOM2
194276400456	0	NULL	NEWPROD

Problem #1 is I don't quite understand how this works... I mean, value 0 isn't '', but still for the combination of NULL 0 NULL I get an empty value, which is what I wanted... But how does it do that?

And also, can someone update my final query to comma-separate the calculated TAGS column? Problem #2 is that I don't want it to contain just two commas, like ,,, when the combination wouldn't justify any of the three values to appear... It should work like PHP's implode() works...

To help you help me with this, I'm including a fiddle with the setup of the scenario I describe here... Thanks in advance!

Answer 1

Use the CONCAT_WS() function to concat values into a comma (or other separator) separated list, which ignores nulls.

To use CONCAT_WS(), you want to pass it a true NULL if the value is "blank" (by your definition), otherwise your custom label:

SELECT DISTINCT
  FLDSTRING1,
  FLDFLOAT5,
  FLDFLOAT6,
  CONCAT_WS(',',
    CASE WHEN FLDSTRING1 IS NULL OR FLDSTRING1 = '' OR FLDSTRING1 = '0' THEN NULL ELSE 'NEWPROD' END,
    CASE WHEN FLDFLOAT5 IS NULL OR FLDFLOAT5 = 0 THEN NULL ELSE 'CUSTOM1' END,
    CASE WHEN FLDFLOAT6 IS NULL OR FLDFLOAT6 = 0 THEN NULL ELSE 'CUSTOM2' END) AS TAGS
FROM MATERIAL

Replaced GROUP BY with DISTINCT because it's simpler and (here) achieves the same thing.

---

If CONCAT_WS is not available:

SELECT DISTINCT
  FLDSTRING1,
  FLDFLOAT5,
  FLDFLOAT6,
  REPLACE(REPLACE(REPLACE(CONCAT(
    CASE WHEN FLDSTRING1 IS NULL OR FLDSTRING1 = '' OR FLDSTRING1 = '0' THEN 'X' ELSE 'NEWPROD' END,
    ',',
    CASE WHEN FLDFLOAT5 IS NULL OR FLDFLOAT5 = 0 THEN 'X' ELSE 'CUSTOM1' END,
    ',',
    CASE WHEN FLDFLOAT6 IS NULL OR FLDFLOAT6 = 0 THEN 'X' ELSE 'CUSTOM2' END
  ), ',X', ''), 'X,', ''), 'X', '') AS TAGS
FROM MATERIAL

See dbfiddle.

Answer 2

Since you are using SQL Server 2014, instead of CONCAT_WS you may try STUFF as shown below. By prepending the delimiter , before all strings ,the STUFF will remove the first comma found.

SELECT 
    FLDSTRING1, 
    FLDFLOAT5, 
    FLDFLOAT6, 
    STUFF(
        CONCAT(
            CASE WHEN FLDSTRING1 IS NULL OR FLDSTRING1 IN ('0','') THEN '' THEN '' ELSE ',NEWPROD' END, 
            CASE WHEN FLDFLOAT5 IS NULL THEN '' ELSE ',CUSTOM1' END,
            CASE WHEN FLDFLOAT6 IS NULL THEN '' ELSE ',CUSTOM2' END
        ),
        1,1,''
    ) AS TAGS
FROM @MATERIAL
GROUP BY FLDSTRING1, FLDFLOAT5, FLDFLOAT6;

View working demo db fiddle

Let me know if this works for you.