-1

I need some help, in my database I have a relation many to many, so I created an aux table to make 1 to many relationship, the tables are the next.

offices           request        request_x_offices
-------          ---------       ------------------ 
id               id              id_request
office_name      name            id_office

So when i do a query to request_x_offices i get some like the next

request_x_offices
-----------------
id_request| id_office
-----------------
1   1
1   2
1   3
1   4
1   5 
2   1
2   2 
2   3    etc...

after I do a JOIN to replace the offices_id by the name of the office and I get the next

id_request|office_name
-----------------
1 | office_name1
1 | office_name2
1 | office_name3
2 | office_name1
2 | office_name3
2 | office_name5

But I want to get something like the next.

------
id_request|office_name
1 |office_name1, office_name2, office_name3
2 |office_name1, office_name3, office_name5

I tried do a coalesce but I only get a single row with all the office_name of all the id's :(

Any help will be appreciated, thanks for you time guys.

Mitch3091
  • 4,292
  • 3
  • 17
  • 23
  • Google: "SQL Server string aggregation". – Gordon Linoff Oct 21 '15 at 19:46
  • 1
    That is not what coalesce does. coalesce returns the first nonNULL value in the list of parameters. What you want it to generate a delimited list of values. Here is an article that explains one way to do this. http://www.sqlservercentral.com/articles/comma+separated+list/71700/ – Sean Lange Oct 21 '15 at 19:46
  • Usually you'd want to leave the data as it is shown in your second last table. You can always comma delimit the data when you do the SELECT to get it out of the table. – Dave.Gugg Oct 21 '15 at 19:53

1 Answers1

0

Try with this:

SELECT p1.ID,
       ( SELECT NAME + ' and ' 
           FROM YourTable  p2
          WHERE p2.ID = p1.ID
          ORDER BY NAME
            FOR XML PATH('') ) AS Name,
        sum(Amount)
      FROM YourTable p1
      GROUP BY ID ;
bugs2919
  • 371
  • 1
  • 8