CODEWITHSUNDEEP
sqlsql-server
Jamie Sutton
Jamie Sutton

Reputation: 163

Concatenate results with some null values

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.

Upvotes: 2

Views: 53

Answers (2)

Vitaly Borisov
Vitaly Borisov

Reputation: 1193

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269623

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;

Upvotes: 4

Related Questions