t-sql query to concatenate corresponding rows

I have a table Managers. Columns: ManagerId, ManagerName.

I have a table Clients. Columns: ClientId, ManagerId, ClientName, ClientAddress, Details.

Each (every?) manager have clients in clients table.

I need a query that returns next table. Columns: Column1, Column2.

Column1: ManagerName

Column2: ClientName1 ',' ClientAddress1 ',' Details1 ';' ClientName2 ',' ClientAddress2 ',' Details2 ';' etc

In column2 just a list of clients which correspond to manager from column1.

How can I do that?

I guess I need to use COALESCE b开发者_运维百科ut I'm not sure.

Same as previous answers with the addition to handle null values and XML special characters <>&'".

declare @Managers table
(
  ManagerId int,
  ManagerName varchar(50)
)

declare @Clients table
(
  ClientId int,
  ManagerId int,
  ClientName varchar(50),
  ClientAddress varchar(50),
  Details varchar(50)
)

insert into @Managers values(1, 'Manager 1')
insert into @Managers values(2, 'Manager 2')

insert into @Clients values (1, 1, 'Client 1',   'CA 1', 'D 1')
insert into @Clients values (2, 1, 'Client 2',   'CA 2', 'D 2')
insert into @Clients values (3, 2, 'XML special characters &<>" and null values', null,  'D 3')

select M.ManagerName,
       stuff((select '; '+
                     coalesce(C.ClientName, '')+', '+
                     coalesce(C.ClientAddress, '')+', '+
                     coalesce(C.Details, '')
              from @Clients as C 
              where C.ManagerId = M.ManagerId
              for xml path(''), type).value('.', 'nvarchar(max)'), 1, 2, '') as Clients
from @Managers as M

Result:

ManagerName  Clients
-----------  ---------------------------------------------------
Manager 1    Client 1, CA 1, D 1; Client 2, CA 2, D 2
Manager 2    XML special characters &<>" and null values, , D 3

Here is a working example using XML AUTO

With Managers AS
(
      Select 1 as ManagerId, 'Tom' as ManagerName
UNION Select 2 as ManagerId, 'Jane' as ManagerName
),
Clients as
(
      Select 1 as ClientId, 1 as ManagerId, 'TaXon Pro' as ClientName, '112 Acme St Cityville DD 56' as ClientAddress, 'ABC' as Details
UNION Select 2 as ClientId, 1 as ManagerId, 'Pro Xon' as ClientName, '12342 Bylo Rd Streetville DD 156' as ClientAddress, 'CDR' as Details
UNION Select 3 as ClientId, 1 as ManagerId, 'Clean Svc' as ClientName, '6512 Toni St Townville DD 1236' as ClientAddress, 'D@#$' as Details
UNION Select 4 as ClientId, 2 as ManagerId, 'ContraRel' as ClientName, '152 Acme St Villageville DD 3456' as ClientAddress, 'SER' as Details
UNION Select 5 as ClientId, 2 as ManagerId, 'RepoIn' as ClientName, '1 Acme St Districtville DD 1456' as ClientAddress, 'KH' as Details
)
SELECT 
    M.ManagerName,
    (
        SELECT c.ClientName + ', ' + C.ClientAddress + ', ' + c.Details + ';'
        FROM Clients C
        WHERE m.ManagerId = c.ManagerId
        FOR XML PATH('') 
    ) AS Clients
FROM Managers M
GROUP BY 
    m.ManagerId, M.ManagerName

The output will look like this

ManagerName Clients
----------- -------------------------------------------------------------------------------------------------------------------------------------------
Tom         TaXon Pro, 112 Acme St Cityville DD 56, ABC;Pro Xon, 12342 Bylo Rd Streetville DD 156, CDR;Clean Svc, 6512 Toni St Townville DD 1236, D@#$;
Jane        ContraRel, 152 Acme St Villageville DD 3456, SER;RepoIn, 1 Acme St Districtville DD 1456, KH;

(2 row(s) affected)

SELECT 
    ManagerName,
    (
        SELECT ClientName + ', ' + ClientAddress + ', ' + Details + ' ; '
        FROM Clients c
        WHERE c.ManagerId = m.ManagerId
        FOR XML PATH ('')
    ) AS clients
FROM Managers m