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.
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.
Comments