tsql aggregate string for group by

I have two tables:

Names(id, name)
Addresses(id, name_id, address)

  • Transaction (Process ID) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction
  • How to pass Table-Valued parameters from java to sql server stored procedure?
  • comparing two tables
  • How to pass a table variable from one stored procedure to another
  • How can I run my custom function and query in a loop for different time frames?
  • How to execute trigger after import?
  • I want to write query that return me:
    name, address list (address1, address2, adress3, ..)

    Something like:

    Select A.name, B.list_of_addresses
    From Names A
        Inner Join (Select name_id, /*list_of_addresses with comma between them*/
                    From Addresses
                    Group By name_id)  B ON A.id=B.name_id

    One Solution collect form web for “tsql aggregate string for group by”

    You can use For XML as a trick to achieve that from SQL Server 2005 onwards.

            select ',' + B.address
            from Addresses B
            WHERE A.id=B.name_id
            for xml path('')),1,1,'')
    From Names A

    It works well if you don’t already have commas in the address, but even if it did, since your request is to put commas between them.. this is probably just as ‘right’.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.