Concatenate results with some null values

Jamie Sutton

Scenario: I have a table which returns markets where my fruit CAN NOT be sold. There can be up to 4 different conditions which exclude a market from fruit.

My code is:

SELECT DISTINCT RPin, Variety, SubDivisionId, BlockId
, STUFF((
    SELECT ',' + CAST(ClearanceID as varchar)
    FROM [RPin_ExclusionT] t1
    where t1.RPin = t2.RPin AND t1.Variety = t2.Variety AND t1.SubDivisionId = t2.SubDivisionId AND t1.BlockId = t2.BlockId
    order by t1.ClearanceID 
    FOR XML PATH('')
), 1, 1, '') AS Clearance
FROM [RPin_ExclusionT] t2

For example:

Rpin   Variety   Subdivision   Block   Clearance
1234   039       B             A       RUS
1234   039       B             A       CHN

Would result in:

Rpin   Variety   Subdivision   Block   Clearance
1234   039       B             A       RUS, CHN

My code works if every field has data..Sometimes, a clearance may only have an RPin with everything else a NULL

Rpin   Variety   Subdivision   Block   Clearance
1234   039       B             A       RUS
1234   039       B             A       CHN
1234   NULL      NULL          NULL    JAP
1234   NULL      NULL          NULL    TWN

I was this to return the same results, as before

Rpin   Variety   Subdivision   Block   Clearance
1234   039       B             A       RUS, CHN
1234   NULL      NULL          NULL    JAP, TWN

However, anywhere there is a NULL value in any column results in a NULL value in my clearance column.

Rpin   Variety   Subdivision   Block   Clearance
1234   039       B             A       RUS, CHN
1234   NULL      NULL          NULL    NULL

I'm intermediate at best with SQL and can't quite adjust the query to do as above ... any pointers would be greatly appreciated.

Gordon Linoff

I prefer to do the distinct before the subquery. You need NULL-safe comparisons:

SELECT RPin, Variety, SubDivisionId, BlockId,
       STUFF( (SELECT ',' + CAST(re2.ClearanceID as varchar(255))
               FROM RPin_ExclusionT re2
               WHERE re2.RPin = re.RPin AND
                     (re2.Variety = re.Variety OR re2.Variety IS NULL and re.Variety IS NULL) AND
                     (re2.SubDivisionId = re.SubDivisionId OR re2.SubDivisionId IS NULL AND re.SubDivisionId IS NULL) AND
                     (re2.BlockId = re.BlockId OR re2.BlockId IS NULL AND re.BlockId IS NULL)
               ORDER BY re2.ClearanceID 
               FOR XML PATH('')
              ), 1, 1, '') AS Clearances
FROM (SELECT DISTINCT RPin, Variety, SubDivisionId, BlockId
      FROM RPin_ExclusionR re
     ) re;

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

Bigquery replacing empty results or null values with some 000

How do you concatenate multiple columns in a DataFrame into a another column when some values are null?

Oracle concatenate String null values

Concatenate multiple columns, with null values

Concatenate multiple values and removing characaters when null

Concatenate two columns of spark dataframe with null values

concatenate values in dataframe if a column has specific values and None or Null values

Results not showing in MySQL with NULL values

Sort results by number of NOT NULL values

mysql INSERT INTO results in NULL values

How to return null in SUM if some values are null?

SQL Server : concatenate values and ignore null or blank values

Concatenate string field using dplyr when some values are NA

Jackson: remove some values from json and keep some null values

pyspark replacing null values with some calculation related to last not null values

Concatenate values

Concatenate multiple columns of dataframe with a seperating character for Non-null values

How to concatenate two columns that might contain NULL values in a select statement?

Concatenate string values with delimiter handling null and empty strings in java 8?

Concatenate string values with delimiter handling null and empty strings?

MYSQL order by - NULL values impacting my results

Mysql select ignores null values on return results

NULL values behaviour in BigQuery Queries/Results

SQL null values not being shown in results

How to remove empty results (NULL values) in SQL

MySQL - selecting rows with some values being null

asserting array equalTo in PHPUnit with some values null

duplicate ids are coming into result with some null values

How to have some null and empty values in a Group By