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
al4629740Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Koen Van WielinkBusiness Intelligence SpecialistCommented:
Since you did not provide your table structures and corresponding sample data I had to make a bunch of assumptions on what the data looks like, but based on your expected result this should get you going:

create table #tblOrgProfile
(	Agency	nvarchar(100)
,	AgencyId int
,	CSWGroup int
)

Create table #tblOrgMonthlyReport
(	Mnth	int
,	AgencyId	int
,	MonthlyId	int
)

Create table #tblOrgMonthlyLinkages
(	MonthlyId	int
,	LinkageName	nvarchar(100)
,	Linkage		nvarchar(100)
)

insert into #tblOrgProfile
values	('Beacon Hill Community Improvement Assoc', 1, 1)
	,	('Better Life for Youth', 2, 1)
	,	('Greater Roseland Community Committee', 3, 1)
	
insert into #tblOrgMonthlyReport
values	(1, 1, 1)
	,	(1, 2, 2)
	,	(1, 3, 3)
	
Insert into #tblOrgMonthlyLinkages
values	(1, 'Whitier Elementary', 'Provide Prevention services to youth ages 11-17')
	,	(1, 'Whitier Elementary', 'Sevice Agreement')
	,	(2, 'Bethel Lutheran  Church', 'Provided  Space and  Donate Food')
	,	(2, 'HOLLYWOOD DREAMS', 'MENTORING Youth')
	,	(3, 'new life baptist church', 'to provide housing for the program')
	,	(3, 'n/a', 'n/a')

;With CTE
(	RowNum
,	AgencyId
,	Agency
,	Mnth
,	LinkageName
,	Linkage
)

as

(
Select		a.RowNum
		,	a.AgencyId
		,	a.Agency
		,	a.Mnth
		,	Cast(a.LinkageName as nvarchar(max))
		,	Cast(a.Linkage as nvarchar(max))
from	(select		ROW_NUMBER() over (partition by p.agencyId order by p.agencyId) as 'RowNum'
				,	p.AgencyId
				,	P.Agency
				,	M.Mnth
				,	L.LinkageName
				,	L.Linkage 
		from	#tblOrgProfile P
				inner join #tblOrgMonthlyReport M 
					on P.AgencyID = M.AgencyID
				inner join #tblOrgMonthlyLinkages L 
					on L.MonthlyID = M.MonthlyID
		where	P.CSWGroup = 1) as a
where	a.RowNum = 1

union all

Select		b.RowNum
		,	b.AgencyId
		,	b.Agency
		,	b.Mnth
		,	CTE.LinkageName + '; ' + CAST(b.LinkageName as nvarchar(max))
		,	CTE.Linkage + '; ' + CAST(b.Linkage as nvarchar(max))
From	(select		ROW_NUMBER() over (partition by p.agencyId order by p.agencyId) as 'RowNum'
				,	p.AgencyId
				,	P.Agency
				,	M.Mnth
				,	L.LinkageName
				,	L.Linkage 
		from	#tblOrgProfile P
				inner join #tblOrgMonthlyReport M 
					on P.AgencyID = M.AgencyID
				inner join #tblOrgMonthlyLinkages L 
					on L.MonthlyID = M.MonthlyID
		where	P.CSWGroup = 1) as b
		inner join CTE on b.AgencyId = CTE.AgencyId
		and	b.RowNum > CTE.RowNum
)

select		CTE.Agency
		,	CTE.LinkageName
		,	CTE.Linkage
from	CTE
		inner join (select		AgencyId
							,	MAX(rowNum) as RowNum
					from	CTE
					group by AgencyId) as CTE2
			on cte.AgencyId = CTE2.AgencyId
			and cte.RowNum = CTE2.RowNum
order by cte.AgencyId

drop table #tblOrgProfile
drop table #tblOrgMonthlyReport
drop table #tblOrgMonthlyLinkages

Open in new window


It uses a recursive CTE to first extract the first row for each agency, and then adds to it the existing record plus the linkage information for the next record. At the end only the records with the maximum rowcount are selected which contain all the linkages.
Note that there might be an easier way to this than to use the row_number function, but since we don't know what the rest of your data looks like this is all I had to go on. There might be a better way to identify unique records.
Anyway, when I run this it matches your expected result so I hope it works.
Next time, please post a bit more data and your table structures (at least the relevant columns).
Thanks.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
HainKurtSr. System AnalystCommented:
here is a sample

with t as (
select 1 id, 'HK' name 
union select 1, 'AB' 
union select 2, 'C' 
union select 2, 'D' 
union select 2, 'EE' 
)
select distinct t1.id, Stuff((Select ', ' + Name From t t2 where t2.id=t1.id FOR XML PATH('')), 1 , 2,'') names
from t t1

id	names
1	AB, HK
2	C, D, EE

Open in new window

0
HainKurtSr. System AnalystCommented:
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...
0
PortletPaulfreelancerCommented:
"for xml path" will produce the wanted concatenations & I recommend using CROSS APPLY for this:
SELECT
      P.Agency
    , CA.LinkageName
    , CA.Linkage
FROM tblOrgProfile P
    CROSS APPLY (
        SELECT
          STUFF((
                SELECT
                       ';' + L.LinkageName 
                FROM tblOrgMonthlyReport M
                JOIN tblOrgMonthlyLinkages L ON M.MonthlyID = L.MonthlyID
                WHERE P.AgencyID = M.AgencyID
                FOR XML PATH ('')
                )
             , 1, 1, '')
         , STUFF((
                SELECT
                       ';' + L.Linkage 
                FROM tblOrgMonthlyReport M
                JOIN tblOrgMonthlyLinkages L ON M.MonthlyID = L.MonthlyID
                WHERE P.AgencyID = M.AgencyID
                FOR XML PATH ('')
                )
             , 1, 1, '')
         ) CA (LinkageName, Linkage)
;

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.