asked on

combine multiple results into a unique row

I have a query where I get the results from a Join. However, what I would like to achieve is combining all the multiple results into one row for each unique Agency. Please see the attached file. How can I adjust the code to achieve the desired results.

Here is the query that I am using:

select P.Agency,M.Month,L.LinkageName,L.Linkage from tblOrgProfile P
join tblOrgMonthlyReport M on P.AgencyID = M.AgencyID
join tblOrgMonthlyLinkages L on L.MonthlyID = M.MonthlyID
where P.CSWGroup = 1
order by Agency

Open in new window

Results and Desired Results in the attachment
Output-and-Desired-Output.xlsx

ASKER CERTIFIED SOLUTION

Koen Van Wielink

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

HainKurt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

HainKurt

in your case, say your tables are

customers (cid, cname)
types (cid, tname)
services (cid, sname)

select cid, cname,
Stuff((Select ', ' + tName From types t where t.cid=c.sid FOR XML PATH('')), 1 , 2,'') types,
Stuff((Select ', ' + sName From services s where s.cid=c.cid FOR XML PATH('')), 1 , 2,'') services
from customers
order by cid

Open in new window

should work...

SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial