>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


Unfortunately MS SQL server doesn't have built-in functions to do that simply as in some other databases but this would do anyway:

IF EXISTS ( SELECT  *
            FROM    tempdb.sys.objects
            WHERE   object_id = OBJECT_ID(N'[tempdb]..[#crsLines]', 'U') )
    DROP TABLE #crsLines;
IF EXISTS ( SELECT  *
            FROM    tempdb.sys.objects
            WHERE   object_id = OBJECT_ID(N'[tempdb]..[#summary]', 'U') )
    DROP TABLE #summary;

CREATE TABLE #crsLines
    (
      part_No VARCHAR(10) ,
      sono VARCHAR(10)
    );
CREATE TABLE #summary
    (
      part_No VARCHAR(10) ,
      sono VARCHAR(MAX) ,
      otherField INT
    );

INSERT  INTO #crsLines
        ( part_No, sono )
VALUES  ( 'PN1', 'SO1' ),
        ( 'PN1', 'SO2' ),
        ( 'PN1', 'SO5' ),
        ( 'PN2', 'SO1' ),
        ( 'PN2', 'SO3' ),
        ( 'PN4', 'SO2' ),
        ( 'PN4', 'SO4' );


INSERT  #summary
        ( part_No, sono, otherField )
VALUES  ( 'PN1', '', 1 ),
        ( 'PN2', '', 2 ),
        ( 'PN3', '', 3 ),
        ( 'PN4', '', 4 );


WITH    distinctParts
          AS ( SELECT DISTINCT
                        part_No
               FROM     #crsLines
             ),
        preSummary ( part_no, sono )
          AS ( SELECT   t1.part_No ,
                        STUFF(t3.V, 1, 2, '')
               FROM     distinctParts t1
                        CROSS APPLY ( SELECT    ', ' + sono
                                      FROM      #crsLines t2
                                      WHERE     t1.part_No = t2.part_No
                                    FOR
                                      XML PATH('')
                                    ) t3 ( V )
             )
    UPDATE  #summary
    SET     sono = ps.sono
    FROM    preSummary ps
    WHERE   #summary.part_No = ps.part_no;

SELECT  *
FROM    #summary;