Geek Answers Handbook

T-sql query to concatenate matching rows

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

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

Each (each?) Manager has customers in the customer table.

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

Column 1: ManagerName

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

Column2 is simply a list of clients that match the manager from column1.

How can i do this?

I think I need to use COALESCE, but I'm not sure.

+4
source share
3 answers

Here is an example of working with 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 result 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)
+1
source

Similar to previous answers with the addition of null descriptors and special XML 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
+2
source 
 SELECT ManagerName, ( SELECT ClientName + ', ' + ClientAddress + ', ' + Details + ' ; ' FROM Clients c WHERE c.ManagerId = m.ManagerId FOR XML PATH ('') ) AS clients FROM Managers m
+1
source

More articles:


All Articles