Solved

combine multiple results into a unique row

Posted on 2014-11-20
4
73 Views
Last Modified: 2014-11-21
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
Comment
Question by:al4629740
  • 2
4 Comments
 
LVL 12

Accepted Solution

by:
Koen Van Wielink earned 100 total points
ID: 40456701
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
 
LVL 51

Assisted Solution

by:HainKurt
HainKurt earned 100 total points
ID: 40456711
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
 
LVL 51

Expert Comment

by:HainKurt
ID: 40456715
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
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 300 total points
ID: 40456720
"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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now