Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 84
  • Last Modified:

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
0
al4629740
Asked:
al4629740
  • 2
3 Solutions
 
Koen Van WielinkIT ConsultantCommented:
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
 
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
 
PortletPaulCommented:
"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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now