Not very efficient, but should give the correct results

SELECT s1.well_id,

s1.Top1,

MIN(t1.BASE1) AS BASE1,

(SELECT DISTINCT c.comment AS "text()"

FROM TEMP1 c

WHERE c.well_id = s1.well_id

AND c.Top1 BETWEEN s1.Top1 AND MIN(t1.BASE1)

AND c.BASE1 BETWEEN s1.Top1 AND MIN(t1.BASE1)

ORDER BY c.comment

FOR XML PATH(''),TYPE).value('.','VARCHAR(100)') AS comments

FROM TEMP1 s1

INNER JOIN TEMP1 t1 ON t1.well_id=s1.well_id

AND s1.Top1 <= t1.BASE1

AND NOT EXISTS(SELECT * FROM TEMP1 t2

WHERE t2.well_id=t1.well_id

AND t1.BASE1 >= t2.Top1 AND t1.BASE1 < t2.BASE1)

WHERE NOT EXISTS(SELECT * FROM TEMP1 s2

WHERE s2.well_id=s1.well_id

AND s1.Top1 > s2.Top1 AND s1.Top1 <= s2.BASE1)

GROUP BY s1.well_id,s1.Top1

ORDER BY s1.well_id,s1.Top1;

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537