drew.allen (7/21/2016)

---

Sean Lange (7/21/2016)

---

drew.allen (7/21/2016)

---

Get rid of the ISNULL altogether. FOR XML automatically discards null expressions unless the XSINIL directive is specified.

SELECT DISTINCT STUFF(

(SELECT ', ' + t2.Name

FROM #Temp t2

where t1.ID = t2.ID -- t1.DeliverableITEP = t2.DeliverableITEP AND t1.GeoMarket = t2.GeoMarket AND t1.Segment = t2.Segment

FOR XML PATH (''))

, 1, 1, '') AS Name

FROM #Temp t1

Drew

PS: The estimated plan shows that this is 0.1% faster than using a WHERE clause to exclude NULL values. Of course, I don't put much stock in the estimated plan on such a small table.

Drew this has the problem when there is an empty string for name.

That's what I get for posting when I had trouble sleeping last night. Still, I wonder how it might perform with this change.

SELECT DISTINCT STUFF(

(SELECT ', ' + NULLIF(t2.Name, '')

FROM #Temp t2

where t1.ID = t2.ID -- t1.DeliverableITEP = t2.DeliverableITEP AND t1.GeoMarket = t2.GeoMarket AND t1.Segment = t2.Segment

FOR XML PATH (''))

, 1, 1, '') AS Name

FROM #Temp t1

Unfortunately, I don't have time to do a comparison.

Drew