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

Hello experts,

This has been asked before, but I'm not seeing an exact match on what I need to do.  

Overall, I have a table of sales reps and they make sales with the help of assistants.

I need to send emails to the sales reps and CC: all the assistants who have helped during the months.  The trick is I need to combine the email address from each sales rep (across multiple rows) into a single derived field.

I also need to see a total of all sales that sales rep did across ALL Assistants.

I've attached an Excel example excerpt that really describes this well. A picture is worth a thousand words I guess.

Looking forward to some really great help as usual!

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

This should do the trick

DECLARE @T1 TABLE (ID INT, SalesRep VARCHAR(20), SalesRepEmail VARCHAR(50), TotalSales INT, AssistantEmail VARCHAR(50))

INSERT INTO @T1 VALUES (1, 'Jack Black','jack@test.abc',25,'assistant1@test.abc')
INSERT INTO @T1 VALUES (1, 'Jack Black','jack@test.abc',30,'assistant2@test.abc')
INSERT INTO @T1 VALUES (2, 'John Doe','john@test.abc',15,'assistant1@test.abc')
INSERT INTO @T1 VALUES (2, 'John Doe','john@test.abc',60,'assistant3@test.abc')
INSERT INTO @T1 VALUES (3, 'Mary Jane','mary@test.abc',90,'assistant4@test.abc')
INSERT INTO @T1 VALUES (3, 'Mary Jane','mary@test.abc',5,'assistant5@test.abc')

;WITH cteGrp AS (
SELECT T1.ID, T1.SalesRep, T1.SalesRepEmail, sum(T1.TotalSales) AS TotalSales
GROUP BY T1.ID, T1.SalesRep, T1.SalesRepEmail
SELECT cteGrp.*,
                    SELECT ';' + t1.AssistantEmail
                    FROM @T1 T1
                    WHERE t1.ID = cteGrp.ID
                    FOR XML PATH ('')
                   ), 1, 1, '') AS AssistantList
FROM cteGrp

First we create the summary table, then we do some magic with XML and stuff to get the email list.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dpmoneyAuthor Commented:
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:
              , T1.SalesRep
              , T1.SalesRepEmail
              , SUM(T1.TotalSales) AS TotalSales
        FROM @T1 T1
        GROUP BY T1.ID
               , T1.SalesRep
               , T1.SalesRepEmail
     ) AS s
                        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)

Open in new window

The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

dpmoneyAuthor Commented:
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.
dpmoneyAuthor Commented:
@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!

dpmoneyAuthor Commented:
Thanks - fantastic!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.