SQL 2005 - Combine data from field across multiple rows into a derived field on a single row

Thanks.  I'll test it on Wed, 9/4 when I get back to the office and will report back.

no points please

you will want to add 'distinct' after " stuff (( select " in the above

as an alternate to a CTE, you can do this:

SELECT
        *
FROM (
        SELECT
                T1.ID
              , T1.SalesRep
              , T1.SalesRepEmail
              , SUM(T1.TotalSales) AS TotalSales
        FROM @T1 T1
        GROUP BY T1.ID
               , T1.SalesRep
               , T1.SalesRepEmail
     ) AS s
CROSS APPLY (
        SELECT
                STUFF((
                        SELECT DISTINCT -- this is where distinct is recommended
                        ';' + t1.AssistantEmail
                        FROM @T1 T1
                        WHERE t1.ID = s.ID
                        FOR xml PATH ('')
                        )
                , 1, 1, '')
                ) AS ca1 (Distribution_List)

Select allOpen in new window

This is not translating perfectly into my production field names so I need to work on it a bit tomorrow.  More details to follow- thanks for your patience.

>>This is not translating perfectly into my production field names
it is my opinion that it is way easier for all concerned (and faster to full resolution) if we work with the real table & field names. re-usable sample data also hastens time to solution.

@PortletPaul - Thanks for your feedback on both occasions.  Interestingly, adding distinct did not seem to make a different in the resulting recordset.  The original solution from lluddenworked perfectly.  With regard to using fake field names, I only modify them slightly because I'm not at liberty to expose internal field names in a public forum.

@lludden - worked great.  As noted above, I just had to adjust it a bit to match my true field names.  Also, the data was not really coming from a table, but rather, a derived table based on values from a few different tables.  I didn't want to complicate the question on Experts Exchange with that level detail so I created the simple example which paid off.  I needed some help to get me over the finish line and your solution was perfect!

Thanks!

Thanks - fantastic!