Concatenate results with some null values

Question

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.

This may help: w3schools.com/sql/sql_isnull.asp

Andrew
– Andrew

2019-02-21 22:20:29 +00:00
Commented Feb 21, 2019 at 22:20 — Andrew
– Andrew, Commented Feb 21, 2019 at 22:20

Gordon Linoff · Accepted Answer · 2019-02-21 22:20:48Z

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;

This is the better answer here, as it provides a SARGable answer.
I was too quick, by comparison your query performs much better. Cheers.

Vitaly Borisov · Accepted Answer · 2019-02-21 22:21:09Z

Assuming all fields are varchars - please try this:

SELECT DISTINCT RPin, Variety, SubDivisionId, BlockId ,STUFF(( SELECT ',' + CAST(ClearanceID AS VARCHAR(8000)) FROM [RPin_ExclusionT] t1 WHERE COALESCE(t1.RPin,'') = COALESCE(t2.RPin,'') AND COALESCE(t1.Variety,'') = COALESCE(t2.Variety,'') AND COALESCE(t1.SubDivisionId,'') = COALESCE(t2.SubDivisionId,'') AND COALESCE(t1.BlockId,'') = COALESCE(t2.BlockId,'') ORDER BY t1.ClearanceID FOR XML PATH('') ), 1, 1, '') AS [Clearance] FROM [RPin_ExclusionT] t2

Collectives™ on Stack Overflow

Concatenate results with some null values

2 Answers 2

2 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Related