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 whenFLDSTRING1
would evaluate totrue
- anything not NULL, 0, or ''CUSTOM1
for whenFLDFLOAT5
would evaluate totrue
- basically the value 1CUSTOM2
for whenFLDFLOAT6
would evaluate totrue
- 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!
CodePudding user response:
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;
Let me know if this works for you.
CodePudding user response:
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.