I have a table #crsLines with fields Part_No,Sono
I want to capture all the Sono for a particular Part_no

DECLARE @sono VARCHAR(500) = ''

SELECT @sono = @sono + ', ' + p.sono FROM (SELECT DISTINCT sono FROM #crslines WHERE #crslines.cpart_no = 'INT-199-00853') p
SET @sono = SUBSTRING(@sono,3,5000)

I now need to extend this a bit. I have a summary table with several fields including Part_no and Sono (which is empty)

What kind of query would I use to update Sono with the values from #crslines?

(If there's a better algorithm to get this I'm open to suggestion)

Sample Data

#crslines
part_no                    sono
PN1                        SO1
PN1                        SO2
PN1                        SO5
PN2                        SO1
PN2                        SO3
PN4                        SO2
PN4                        SO4

Desired Result in  Summary
part_no                    sono
PN1                        SO1, SO2, SO5
PN2                        SO1, SO3
PN3
PN4                        SO2, SO4

If it makes a difference it is very unlikely that the summary table will have parts that are not in #crslines

Thanks to all ................. Rich