Avatar of dpmoney
dpmoneyFlag for United States of America asked on

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!

Thanks!
SQL-Challenge.PNG
Microsoft SQL ServerMicrosoft SQL Server 2005SQL

Avatar of undefined
Last Comment
dpmoney

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
lludden

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
dpmoney

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

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)

Open in new window

ASKER
dpmoney

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.
Your help has saved me hundreds of hours of internet surfing.
fblack61
PortletPaul

>>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.
ASKER
dpmoney

@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!
ASKER
dpmoney

Thanks - fantastic!
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.