>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
It is a simple query, but normally you don't want to store such information in the table as you would need to parse it back. For concatenating information for each part no look into using FOR XML PATH('') solution.
If it's not broken, fix it until it is.
My Blog