How to get value on a single row with XPath?

Question

I have a user table, email table and a relational email table. Some users have more than one email addresses. When Use

select U.Id,U.Name,E.Name from RelationalEmail AS RE
INNER JOIN Email AS E ON E.Id = RE.EmailId
INNER JOIN User AS U ON U.Id=RE.userId

Of course, I get the result like this:

UserId    Name     Email
1         Olcay    abc@.com
1         Olcay    acc@.com
2         John     jhn@.com
2         John     hnJ@.com

But I don't want repeat of users, I try to get a result like:

UserId    Name     Email      
1         Olcay    abc@.com, acc@.com
2         John     jhn@.com, hnJ@.com

My Person Table:

  UserId    Name      
    1         Olcay    
    2         John

My Email Table:

EmailId    Name
   7         abc@.com
   8         acc@.com
   9         jhn@.com
   10        hnJ@.com

My RelationalEmail Table:

Id   userId  EmailId
  1      1     7
  2      1     8
  3      2     9
  4      2     10

My teacher said I should do that with XPath. But I don't know XPath. I searched that but I couldn't find an answer for my question or I couldn't understand.

Thanks in advance

Check out [this](http://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server) answer. — Asier Azkuenaga, Feb 23 '17 at 15:31
Share more details. Currently I don't see how this issue is related to `XPath` — Andersson, Feb 23 '17 at 15:40

score 2 · Accepted Answer · answered Feb 23 '17 at 15:51

If it is always two emails per name, then here is one way

select UserId, 
       Name, 
       Email = Min(Email),
       Email2 = Max(Email)
From yourtable 
Group by UserId, 
         Name

If you can have N number of emails per name then you need dynamic pivot.

How to get value on a single row with XPath?

1 Answers1