Solved

combine multiple results into a unique row

Posted on 2014-11-20
4
77 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 13

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 53

Assisted Solution

by:Huseyin KAHRAMAN
Huseyin KAHRAMAN 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 53

Expert Comment

by:Huseyin KAHRAMAN
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Percentage Formula 7 33
SQL Job Failed 6 30
SQL: Default Database Integrity Jobs Failing 6 16
Query Task 8 14
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

732 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