SQL Aggregation of text on joined table

Question

Pardon the lack of correct terminology, I'm a professional software engineer usually dealing with Direct3D frameworks. I'm self taught on databases.

I have a _People table and an _Ethnicities table. Since people may have more than one cultural group I have a link table _linkPersonEthnicity. Sample data is shown below:

SampleData

What I want is output in the following form:
enter image description here

To illustrate the problem I present the following (runnable) query:

select lPE.Person, Sum(E.ID) as SumOfIDs,
   Ethnicity = stuff(
      (select ', ' + Max(E.Name) as [text()]
        from _linkPersonEthnicity xPE
        where xPE.Person = lPE.Person
        for xml path('')
      ),
    1, 2, '')
from _Ethnicities E 
join _linkPersonEthnicity lPE on lPE.Ethnicity = E.ID
group by lPE.Person

It returns the Person's ID, a sum of the IDs found for the person's ethnicity, and concatenates the maximum Name with commas. The data is grouped correctly, and the SumOfIDs works, proving the correct data is used.

Naturally I would like to take away the Max aggregate function, but cannot since it is not in the group by list.

Any ideas how to make this work?

Thanks in advance,

AM

(Many thanks to other answers on StackOverflow for getting me this far! Particiularly @Jonathan Leffler for his explanation of the partitioning proceess and @Marc_s for illustrating a text concatenation technique.)

I've also tried coalesce from an answer to concatenating strings by @Chris Shaffer

declare @Names VARCHAR(8000)
select @Names = COALESCE(@Names + ', ', '') + E.Name 
  from _Ethnicities E join _linkPersonEthnicity lPE on lPE.Ethnicity = E.ID
 where lPE.Person = 1001;
select @Names

Same problem. If I remove the where and add group by the text field Name cannot be accessed.

You don't need an aggregate inside the subquery. Did you try and remove it? Also please see [this article](http://sqlperformance.com/2014/08/t-sql-queries/sql-server-grouped-concatenation) and [this follow-up](http://sqlperformance.com/2014/08/t-sql-queries/sql-server-grouped-concatenation-2). — Aaron Bertrand, May 28 '15 at 01:53
@AaronBertrand - Off topic question : Where can i find the list of all articles that you have written. If possible share me a link. Thanks in advance. — Pரதீப், May 28 '15 at 02:21
@AaronBertrand - Thank you sir. Will search in these sites. I found this http://sqlperformance.com/author/abertrand. — Pரதீப், May 28 '15 at 02:35
@AndrewMalcolm, it looks like you accidentally created two accounts. If you sign in with your account used to ask this question, you can edit this post without having to push it into the review queue. http://stackoverflow.com/users/4946944/andrew-malcolm and.... http://stackoverflow.com/users/4604363/andrew — Daniel Nugent, May 28 '15 at 04:17
@DanielNugent - first time I've ever done the 'login with Google' thing. Ironic as I've always been skeptical of linked IDs, but my thinking was if it will work anywhere it would work on a big tech site! Obviously something glitched. — Andrew Malcolm, May 28 '15 at 11:06

Gordon Linoff · Accepted Answer · 2015-05-28T02:24:00.297

1

If I understand correctly, you need for the join to be in the subquery rather than the outer query

select lPE.Person, Sum(lpe.ethnicity) as SumOfIDs,
       Ethnicity = stuff((select ', ' + E.Name as [text()]
                          from _linkPersonEthnicity lPE2 join
                               _Ethnicities e
                               on lpe2.Ethnicity = e.id
                          where lpe2.Person = lPE.Person
                          for xml path('')
                         ), 1, 2, '')
from _linkPersonEthnicity lPE 
group by lPE.Person;

By the way, do you really want the sum of the ids or a count?

edited May 28 '15 at 02:24

answered May 28 '15 at 01:52

Gordon Linoff

1,242,037
58
646
786

Not sure why or what the typo is but I get `The multi-part identifier "e.ID" could not be bound.` Also I why did the comments for this answer get removed? I've typed this once before, and @GordonLinoff did reply. (Sadly I couldn't fix it with the previous reply.) – Andrew Malcolm May 28 '15 at 11:15
@AndrewMalcolm . . . Your original query has `lPE.Ethnicity = E.ID` for the `join` condition. This has the same condition in the subquery. It should work if the joins in the original query were formulated correctly. – Gordon Linoff May 28 '15 at 11:56
Sorry, I forgot to change the SumOfIDs expression which contained the `E.ID`. (I didn't look at since I'm going to throw it away anyway.) _Apologies_ for the hiccup and many thanks for the solution! . . . (As an append I did try this approach very early on but obviously stuffed up. Ah well.) – Andrew Malcolm May 29 '15 at 03:02
BTW, SumOfIDs was put in there just to test I was getting the correct data back. – Andrew Malcolm May 29 '15 at 03:08

SQL Aggregation of text on joined table

1 Answers1