Solved

combine multiple results into a unique row

Posted on 2014-11-20
4
71 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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
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.

762 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

26 Experts available now in Live!

Get 1:1 Help Now