combine multiple rows into one

LEFT OUTER JOIN (

SELECT ref, STUFF

((SELECT ' ' +

com_text

FROM comm

WHERE ref = q1.ref





ORDER BY comseq ASC

FOR XML PATH('')), 1, 1, '') [Comments]

FROM comm AS q1

GROUP BY ref

) AS Comm ON Comm.ref = x.ref